I am trying to determine the max value of a range of values listed in range F7:F26. But only if a corresponding range E7:E26 contains the value in cell E29 (which is the letter M). If the letter M is not in E7:E26, do not consider its corresponding value in F7:F26 for the max calculation. I have tried "max if" formulas but can't get it to work. I only get the #value!" error.
=MAX(IF(E7:E26=E29,F7:F26,""))
You need to confirm array formulas with CTRL+SHIFT+ENTER not just ENTER; you'll see {Curly Brackets} around the equation in that case.
When I tried it out, it worked just fine.
Also, re: Merged Cells
The cells E23:E26 are merged and filled with "M", but it will only be treated as E23 having the value. For example, if you put 5.0 in F25, it won't change the array formula because as far as the equation is concerned E24:E26 are blank.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
Thanks that worked.
Updated thread question, how can I get it to enter the station number next to the selected value? See new attachment.
Oh that's just a basic lookup with INDEX(MATCH) or whatever.
something like:
Please Login or Register to view this content.
See this version.
ben_hensel...thanks for the help...both solutions worked.
Czelaw...thank you also for the reply. I don't understand it but it appeared it return to correct results.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks