17 replies to this topic

[contactskn]

Dear friends today one of my students asked a doubt to me regarding excel and I am not knowing the answer for it so please help me with some really working answers for the same. The problem is related with Excel and is as follows-
There is a table made in Excel and the fields are as follows-

Maths               S.St.                  Science                 English

the values for example are as follows
89                    84                      98                        68

Now first of all I want to find the maximum out of these values, actually I know that it can be accomplished by =max(a2:d2). and the output here will be 98 but actually I want the output as the subject name of the value which is the highest that is in this example Science. Please help me to get this output. Thanking you all in advance. 
 xl.bmp   162.28K   4 downloads

Edited by contactskn, 10 December 2009 - 09:46 AM.

[shadowx]

Vlookup seems to be what you are after:

http://answers.yahoo.com/question/index?qi...14031032AABaYjB

[anwiii]

i don't know the answer off hand, but i want to clarify for others since your english is a little broken. what you want in your example is not to output "98", but "science". correct? my excel skills have been lost 20 years ago. problem is, this is a spread sheet and not a database. the only way you can relate the #98 to is by the exact location of that number. in this case, the field "c2" then you have to relate c2 to the exact location of the subject which would be c1
so when the fields aren't relative, you just have to exchange the fields somehow where the value of c2 is hidden somehow.
so the only way i can see it being done is having science=c2 and having the output show whatever c2 is equal to.

i remember 25 years ago i was self taught in excel. i also aced it in college and i don't ever recall running in to a problem like this. it has me curious if it can be done....

contactskn, on Dec 10 2009, 03:44 AM, said:

Dear friends today one of my students asked a doubt to me regarding excel and I am not knowing the answer for it so please help me with some really working answers for the same. The problem is related with Excel and is as follows-
There is a table made in Excel and the fields are as follows-

Maths               S.St.                  Science                 English

the values for example are as follows
89                    84                      98                        68

Now first of all I want to find the maximum out of these values, actually I know that it can be accomplished by =max(a2:d2). and the output here will be 98 but actually I want the output as the subject name of the value which is the highest that is in this example Science. Please help me to get this output. Thanking you all in advance. 
xl.bmp

[contactskn]

shadowx, on Dec 10 2009, 10:10 AM, said:

Vlookup seems to be what you are after:

http://answers.yahoo.com/question/index?qi...14031032AABaYjB

Dear friend as per your suggestion I have tried Vlookup but unfortunately not working then here I have tried hlookup and it is working for my example but not completely, What I mean to say is by using hlookup I can show the subject name but the subject names should be given below the values. Is there any ways to show the subject names which is written above the values that is in the second row. So please find the attachment in which the formula used is displayed clearly, now I would like to know what should be the change made in the formula so that the needful could be accomplished. That is the subject names written in the second row be displayed and not that in the fourth row. 

 xl.bmp   214.68K   5 downloads

[shadowx]

I notice the formula shows A3:D4 which i think should be A3:D3 not 4. Might be worth trying correcting that.

What happens if you simply delete the subjects under the numbers?

[anwiii]

i must have a weird version of excel because i can't seem to do some things i should be able to do. anyway, when using vlookup, you are referencing a table. in this case subject and grade(or grade percentage) so the formula should look something like =vlookup(max(xx:xx),xx:xx,1)
lookup, table, column#
number

so going through the formula, you essentially looking up a # which in this case is the max value of a particular cell. then you input the cells where the table is which is the subject and the grades. then you enter the comumn to reference. what i did however, was put the table(subject and grades) in columns a&b instead of using rows 1&2. now it seems as though the forumula only work if the #'s in the table are referenced first, before the subjects. now this is using vlookup. i am assuming hlookup would be how you had the original table setup because vlookup wont work with that since the last # in the formula is a row, not a column.

the problem i am having when testing it out is that the formula is not recognizing the max value within the hlookup and is always referencing the last subject in the table. i can add and take away subjects and scores and it will still be the last subject. this has got me so boggled why it works only when the table is reversed.

contactskn, on Dec 10 2009, 04:45 AM, said:

Dear friend as per your suggestion I have tried Vlookup but unfortunately not working then here I have tried hlookup and it is working for my example but not completely, What I mean to say is by using hlookup I can show the subject name but the subject names should be given below the values. Is there any ways to show the subject names which is written above the values that is in the second row. So please find the attachment in which the formula used is displayed clearly, now I would like to know what should be the change made in the formula so that the needful could be accomplished. That is the subject names written in the second row be displayed and not that in the fourth row. 

xl.bmp

[contactskn]

shadowx, on Dec 10 2009, 12:40 PM, said:

I notice the formula shows A3:D4 which i think should be A3:D3 not 4. Might be worth trying correcting that.

What happens if you simply delete the subjects under the numbers?

Dear friend if I am changing it to d3 then it will give the following result that is it will show an error and if we change the last figure from 2 to 1 then it will only show the greatest value that is 99.
 untitled.bmp   224.8K   4 downloads

[anwiii]

go to the excel forum and post your problem. you might get a quicker answer that way

contactskn, on Dec 11 2009, 01:56 AM, said:

Dear friend if I am changing it to d3 then it will give the following result that is it will show an error and if we change the last figure from 2 to 1 then it will only show the greatest value that is 99.
untitled.bmp

[contactskn]

anwiii, on Dec 10 2009, 03:28 PM, said:

i must have a weird version of excel because i can't seem to do some things i should be able to do. anyway, when using vlookup, you are referencing a table. in this case subject and grade(or grade percentage) so the formula should look something like =vlookup(max(xx:xx),xx:xx,1)
lookup, table, column#
number

so going through the formula, you essentially looking up a # which in this case is the max value of a particular cell. then you input the cells where the table is which is the subject and the grades. then you enter the comumn to reference. what i did however, was put the table(subject and grades) in columns a&b instead of using rows 1&2. now it seems as though the forumula only work if the #'s in the table are referenced first, before the subjects. now this is using vlookup. i am assuming hlookup would be how you had the original table setup because vlookup wont work with that since the last # in the formula is a row, not a column.

the problem i am having when testing it out is that the formula is not recognizing the max value within the hlookup and is always referencing the last subject in the table. i can add and take away subjects and scores and it will still be the last subject. this has got me so boggled why it works only when the table is reversed.

Dear friend of mine by applying your suggestion I am getting the first cell value in the range and not the actual answer which I am in need. Because I think vlookup as it is used for vertical lookup so its checking the range vertically and due to which this value is shown that is the first value in the series.



 untitled.bmp   208.9K   2 downloads

[shadowx]

Sorry you will need to use HLOOKUP instead of VLOOKUP (if it exists, if HLOOKUP doesnt exist then you will need to re-arrange the data so it is vertical)

Your formula is slightly wrong though.

You have

Quote

=VLOOKUP(MAX(A2:D2), A2:D2, 1)

Where your numbers are in the cells A2:D2 and your subjects are in A1:D1 (in the second example_

The formula basically says this:

Quote

vertical look up(for the biggest number in cells(A2:D2), Show me the values from the cell range, A2:D2, 1)

I dont know what the 1 at the end is for

So basically you need to make the formula look in the number cells fo rhte highest value and then pick the corresponding subject from the cells ABOVE:

Vertical lookup(Biggest number in(NUMBER:CELLS), Show me the corresponding value from, SUBJECT:CELLS, 1)

EG:

Quote

=HLOOKUP(MAX(A2:*D2), A1:*D1, 1)

Try that In your second exmaple where the numbers are on the second row and the subjects on the first. If you copy and paste it then be aware i have put a * between the colon and the D's as otherwise it gets lumped together as the emoticon. So if you copy and paste remember to take out the *

Edited by shadowx, 11 December 2009 - 09:41 AM.