Excel 2010 :: Finding Data With Tied Maximum Scores

Nov 14, 2012

i have product group,product name and the statistics. I'll use an example of students with score. I have these set of data:

A B C D
1 Student Name Score Sum
2 Student1 Anna 48 80=Sumif(A:A,A2,C:C)
3 Student1 Anna 32 80
4 Student2 Tom 30 80
5 Student2 Tom 30 80
6 Student2 Tom 20 80
7 Student3 June 55 60
8 Student3 June 1 60
9 Student3 June 4 60

Now we have 2 students with tied highest scores with 80 Scores. Naturally i want the rank to be as follow: Top1 Anna since she has the highest score "48", and Top2 Tom, and Top3 June.

The problem is, the score data can vary and Top3 can probably have the highest score and he still didnt make the highest score collectively. And there will also going to be other situations as well because im working on a very large data set, and not these 3 students.

the max score is tied, and since im making another column with sum scores, the data is going to be redundant, and hopefully theres a formula to ignore this.

View 2 Replies


ADVERTISEMENT

Excel 2010 :: Updating Scores For Each Parameter

Apr 5, 2013

I have below format in excel and i will be updating scores for each parameter (column B).

I will be inserting a command/click button and when i click on the button macro should take the name, contact No., scores from column B and paste it in sheet 2 in the same workbook in one row, in the same way when I click the button the scores should be taken from sheet 1 column b and paste one below other row in sheet 2 each time i click on button.

Below is how raw data looks like.

Column A
Column B

Name:
ABC

Contact Number:
XYZ

Parameter
Score

[Code] ..........

Output expected in sheet 2 is as below.

Name:
Contact Number:
Parameter A
Parameter B
Parameter C
Parameter D
Total Score

ABC
XYZ
2
5
6
7
5

I am using Excel 2010.

View 5 Replies View Related

Excel 2010 :: Giving Scores A Numeric Rank

Aug 13, 2014

I have attached an Excel 2010 spreadsheet to show the question.

Essentially, I need to have a formula to indicate the numeric rank of each grade for each student.

I could sort the columns by NAME and SCORE.

However, I would prefer to have a formula that ranks each student's subject grades - some students have multiple numbers that are the same.

View 11 Replies View Related

Excel 2010 :: Maximum Characters In A Cell?

Feb 4, 2014

I was wondering with regards to the max of 32,767 characters in a cell.

1) is it still the same number of characters in excel 2010?

2) is that including spaces or not?

View 7 Replies View Related

Excel 2010 :: Maximum Number Of Nested IF Statements

Jul 29, 2014

1) What's the maximum limit of IF statements in a formula for MS Excel 2010?

2) I have 8 nested IF statements, but I am having trouble with the False part of the argument. I was able to get the False part to work for the 4th argument (because I simply put a comma and closed it's respective colored parenthesis) but the following 3 I can't seem to get and the following message pops when I hit enter "The formula you typed contains an error."

For the False part of the remaining 3 nested statement which is at the end of the formula I just type:

IF($E$2="Q3"........)))IF($E$2="Q2".....)))IF($E$2="Q1")))

And close it's respective colored IF statement argument. I tried putting a comma at the end of each False part for the arguments but it pops with the message "You've entered too many arguments for this function."

View 9 Replies View Related

Excel 2010 :: Print Hundreds Of 6 Column Charts Using Maximum Page For Printing

Apr 28, 2013

I am using Excel 2010

I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.

I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.

How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.

Here is a visual demonstration of how things currently are and how i'd like to get them to be:

As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.

Pic1

How this looks when i try to print, it's only on the left side. right is all blank
Pic2

How i want it to look like upon printing
Pic3

As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.

[URL]

View 7 Replies View Related

Excel 2010 :: Finding Duplicate Numbers?

Jun 15, 2014

Using Office 2010.

I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..

I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.

I get them from different people.

Person A- 234, 569, 498, 849, 848,343,567,347 etc...

Person B- 432, 596, 677, 566, 565,433, 455 etc..

Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.

View 7 Replies View Related

Excel 2010 :: Finding Values Using Linear Extrapolation?

Mar 26, 2014

I am looking to find out certain values from a current set of data using linear extrapolation in Excel 2010.

I have attached the data i am using below:

Data.png

I need to find out the specific power output and heart rate values, at a set blood lactate value (i.e. When the blood lactate value is exactly 2.0mmol.L-1, what is the given power output and heart rate).

I have tried plotting the data into a line graph with a secondary axis, however because i am unable to change the horizontal axis range for the power output values, i cannot interpret the data in this way.

View 14 Replies View Related

Excel 2010 :: Finding Average Value For Positive And Negative Set Of Numbers

Aug 25, 2014

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.

View 4 Replies View Related

Finding Each Time Maximum Value?

Jan 26, 2014

I need a formula in order to find each time the maximum value.

View 3 Replies View Related

Finding 2nd Maximum In An Array

Nov 12, 2008

Very simple problem. I need the second highest value in an array. Max gives me highest. Min gives me lowest. I need a kind of a MAX2 function.

View 3 Replies View Related

A Macro To Finding Maximum Value

May 16, 2009

i have a table same this in 5000 rows and 10 columns that are years from 1998 to 2007. ISO2 is two digit names of countries.

ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007
CL 135 147 180 192 48 629 494 137 135 359
CL 132 145 177 190 45 623 491 134 132 356
CL 129 141 174 186 42 617 488 131 129 353
EC 126 139 171 184 39 611 485 128 126 350
EC 123 135 168 180 36 605 482 125 123 347
EC 120 133 165 178 33 599 479 122 120 344
KH 117 129 162 174 30 593 476 119 117 341
KH 114 127 159 172 27 587 473 116 114 338
KH 111 123 156 168 24 581 470 113 111 335
PH 108 121 153 166 21 575 467 110 108 332
PH 105 117 150 162 18 569 464 107 105 329
PH 102 115 147 160 15 563 461 104 102 326
PH 99 111 144 156 12 557 458 101 99 323
VE 96 109 141 154 9 551 455 98 96 320

i want a macro to advance filter column A and make a unique list of it and then find the maximum value of each country in column A in 10 years. for example final table for country "CL" must be same this

ISO2 D1998 D1999 D2000 D2001 D2002 D2003 D2004 D2005 D2006 D2007
CL 135 147 180 192 629 629 494 137 135 359

View 3 Replies View Related

Finding Equivalent Of Maximum Value In Another Sheet

Jan 26, 2010

I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").

Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.

View 9 Replies View Related

Finding Maximum Value While Excluding Some Values

Feb 2, 2006

I have a row of data with the cells I want to find a MAX for are separated from each other:

a1=300 d1=500 g1=800 j1=10,000

I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.

View 14 Replies View Related

Finding The Cell Address Of Maximum Value

Feb 12, 2007

I have a range of overtime data. I want to list the maximum occurrence of first 10 overtime data ( Like 3200,2950.2300....etc) Thereafter from the results obtained I need to know the cell address of the employee corresponding to the results obtained, i.e XYZ, ABC.....

A B
Employee Name OT Amount
1. XYZ 3200.00
23. ABC 2950.00
35. WER 2300.00

View 10 Replies View Related

Finding The Minimum Value In A Column That Comes After The Maximum Value

Dec 11, 2008

Say I have 2 columns that in basic form look like this:

Column A Column B
Jan 1
Feb 0
Mar 7
Apr 4
May 15
Jun 2
Jul 5
Aug 4

First I want to look up the max value in this column. This is easy =max(b1:b8)
Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.

View 3 Replies View Related

Finding The Maximum Value Of All Those Meeting A Certain Criteria

May 7, 2005

Problem:
Range A2:C10 contains the login and logout times of various ID's.
Each ID could log in and out a number of times a day.
How could we find the first time a specific ID logged in and the last time that same ID logged out?

Solution:
For each of the ID's in range A2:A10, enter two Array Formulas.
To find the first login time (Column B) enter the following formula:
{=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10
To find the last logout time (Column C) enter the following formula:
{=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}

ID______Login Time______Logout Time
1 ______02:40___________03:10
2 ______00:15___________03:20
1 ______06:20___________09:30
3 ______09:14___________11:05
4 ______11:00___________19:30
2 ______04:05___________06:55
3 ______12:08___________17:17
1 ______10:00___________16:20
2 ______08:12___________12:33


ID______First Login Time______Last Logout Time
1 ______2:40__________________16:20
2 ______0:15__________________12:33
3 ______9:14__________________17:17
4 ______11:00_________________19:30

View 9 Replies View Related

Finding Maximum In A Variable Range?

Aug 6, 2013

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.i.e.

A1 Date Price
A2 (New row added,date) (New row added,price)
A3 16/1/2013 5.5
A4 15/1/2013 5.6
A5 14/1/2013 5.45
... ...

Assuming that I am looking for maximum value of the column "Price", since max() cannot be applied to variable range, is there other method I could useto look for the most updated maximum value of column "price"?

View 9 Replies View Related

Finding Maximum Number For A Range

Jul 6, 2009

selecting a range under a condition. I'd like to write a macro to find the maximum value for a certain ID. The number of ID rows (left column) is unknown so can't say range("A1:A5"). Then have the ID and max number shifted to another column. Maybe use 'rows.count' ?

Eg.

4009 5
4009 9
4009 8
4009 4
4005 7
4005 3
4005 9
3008 10
3008 11

View 9 Replies View Related

Finding Maximum Value (but Exclude Blank Cells)

Mar 21, 2014

I am writing a formula for finding a maximum out of three average values. The cells that will be populated with data are A1 to A5; B1 to B5; C1 to C5. The final cell where I want to write the formula needs to show the maximum of the three averages (average of data in each row).

The difficulty is that sometimes only the A and B rows are populated (and sometime only A row is populated) and if B and/or C are blank, the formula fails and shows "#DIV/0!".

How can I write so that if C is blank, then evaluate only A and B data. And If both B and C are blank, then just display the average of row A.

View 3 Replies View Related

Finding The Maximum Value In A Column Based On A Condition

Nov 7, 2008

This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

View 6 Replies View Related

Finding The Maximum Occurrence Of Text Over Non-consecutive Cells

Jun 15, 2009

I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like

Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula

View 3 Replies View Related

Finding The Maximum Number Before A List Turns Negative

May 21, 2009

i have the volume of a reservoir in column A for lets say, 2 years, with monthly data (so 24 numbers in my list)

I also have some variables that feed into this list such as rainfall (fixed), and population (i.e water use, which i can manually change)

if i increase the population too much, the volume of the reservoir will at certain times of the year, fall into negative digits.

what i'm looking for, is a cell which works out the maximum value (in whole numbers) for my population, without any of the numbers in the reservoir volume list falling below zero.

View 9 Replies View Related

Excel 2003 :: Dynamic Chart For Test Scores

Jun 5, 2014

I have 3 test scores for each student. Currently, I have 3 students.

For example :

John 95 90 85
Cindy 50 60 100
Dan 87 86 90

I have a chart that that plots 3 lines, one for each student.

However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.

I am trying to do this in Excel 2003.

View 2 Replies View Related

Stop Excel From Converting Football Scores Into Dates

Jan 11, 2009

http://www.excelforum.com/excel-gene...cognition.html
and im copying and pasting data from a website ( football scores )
and when i get what should be 1-1 it returns 01-jan and this i dont want
i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages
wondered if anyone could work out some syntax to use as a macro button?
claymation had a go but it doesnt work.

View 14 Replies View Related

Excel 2003 :: Index And Match - Table With Header Row Of Scores

Oct 4, 2013

Excel 2003

I have a table with a header row of scores 1-10 in B4:K4.

In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.

What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...

This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?

View 9 Replies View Related

Excel 2010 :: How To Populate Sheet2 From Sheet1 After Finding Matching Word On Sheet2 From List In Sheet1

Oct 4, 2013

I have 2 Worksheets in an Excel 2010 Workbook -

Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"

Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank

What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.

How can I do this?

View 4 Replies View Related

Aggregate Scores For Some Data

Jan 30, 2014

Just wondering the better way to 'get' a score for some data.

For example, I have a data set where there are a number of records are interrogated for validity across X rules. The returned count of errors of course could include one record for all X tests.....

what is the better way of weighting these to get one score

Please see attached example : XL4M.xlsx‎

View 1 Replies View Related

Finding Matching Data From Multiple Columns In 2 Different Excel Files?

Sep 27, 2013

I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.

I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.

View 5 Replies View Related

Find Top X Scores Out Of 50 Scores?

Aug 17, 2009

There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.

=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))

But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved