My question has 2 parts:
I am trying to provide the cell location of the max number in a range. I am using the following formula:
[CODE][=CELL("address",INDEX(Q2,MATCH(MAX(Q2:T2),Q2:T2,0)))/CODE]
Once my cell location is provided, I need to add another formula that say:=IF(S2 is equal to the Max then S1 (which would be Rabbit)
Column Q Column R Column S Column T
R1 Cat Dog Rabbit Fish
R2 5 6 8 2
I know how to write the formula but I am having issuses making them work together
Your original formula should be
Finding the cell above can be simply
Please Login or Register to view this content.
=INDEX(Q1:T1, Match(MAX(Q2:T2), Q2:T2,0))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
ChemistB,
Thank You for your response, I use your suggested formula, it retuns the max value but not the cell location.
Take a look at this. Is this what you wanted?
Yes, exactly!!!
Chemist it works perfectly. Thank You !!
The only issue that I have found when you remove numbers let say they are all zero because no numbers have been entered yet, it defaults to apple, I am assumomg that is because of the fact you have to reference the range with the lables and apple is the first cell in the range. default to apple.png please see the attached.
=IF(SUM(Q2:T2)=0,"",INDEX(Q1:T1,MATCH(MAX(Q2:T2),Q2:T2,0)))
Try this
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Popipopo,
Thank You very much it worked as I nned it to. I did not think of using sum, wow so much to learn, but I like it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks