Return Address of Max Value in a Range --modification needed

[shawb]

Hi all,

I've programmed a worksheet to run a set of quality assurance checks on data from other sheets in the book... In the attached file, the QA assesses data (view sheet notes for details) on the Facility and Project sheets and uses conditional formatting to highlight flags/concerns. 

Starting in C16 on the QA sheet, I'm using the following set of functions within a larger formula to return the address of the building type on the Facility sheet with the largest percent floor area:

INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility"))
So far, this works fine, EXCEPT in one scenario...

MY PROBLEM:

Match returns the row number in terms of the lookup array so in order to get "Facility!E17" instead of E1, I believe I need to use the lookup array highlighted above
I run into an issue when the max value (aka lookup value) is 1 (100% floor area is max) because participants can enter 1 as a response in Facility!E13:15 (Feel free to play with the Facility data to see how this affects QA)
I really don't know what I can do differently so I need help!! How can I modify either the formula (preferable) or the cells on the facility tab so that a 1 is distinguishable from 100%? The building type cells have data validation for decimal 0 and 1 and are formatted for percentages. Since the QA sheet needs to be pasted into 60+ similar files, I'd rather not have to do too much manual manipulation to get this resolved. 

[#2]

How about formatting the input cells as % - then if 1 is entered is displays as 1% whereas the actual value behind it is 0.01 (70% as 0.70 and 100% as 1.00, etc.). I think this should work.

[#3 · Accepted solution]

@shawb 

First, I wonder how you guarantee the quality of your quality assurance checks. Your formulae are, in my opinion, far too complicated, difficult to read, check and maintain. For instance, in sheet QA, cell C16, the formula has 2765 characters. All it does is determine that the largest building is either heated, cooled or both. Finding place number (or index) of the largest percentage in the range where you list the building types (1-5) could be done by the following formula:

=MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)

 It will return either 2, 6, 10, 14, or 18. From that point you can "fairly easily" determine the Yes/No for Heating (1 row below) and Cooling (2 rows below). Like so (taking "only" 557 characters):

=IFS(AND(INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+1,1)="Yes",INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+2,1)="Yes"),"Both",AND(INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+1,1)="Yes",NOT(INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+2,1)="Yes")),"Heated only",AND(NOT(INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+1,1)="Yes"),INDEX(Facility!E16:E35,MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+2,1)="Yes"),"Cooled only")

Still, far from optimal. Using Named ranges with easy to understand names would further reduce the size of this formula.

Then, I actually wonder why you not always enter data for the largest building in the first part, so that you can skip the previous step all together. It would probably simplify most of your other formulae as well. Finding the heating/cooling answer could then be like this:

=IF(Facility!E18="Yes","Heated","- ")&IF(Facility!E19="Yes","/Cooled","/ -")

It will return "Heated/-", "-/Cooled" or "Heated/Cooled". Not sure, though, as I have not managed to go through all of them in detail.

Anyhow, I hope that my thoughts are helpful. If not, you may kindly ignore them.

[#4]

@Riny_van_Eekelen 

Thanks for this reply. 

I think your perception of the formulas being difficult to manage is simply because you didn't write them... obviously, I do not find them difficult to read. 

Of course I do admit I may have complicated things more than necessary.... This bit you suggested:

=MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+2

 SUPER HELPFUL. Honestly, I don't know what was going on with me that I couldn't think of that. 

I find your other suggestions irrelevant or impossible for what I need to get done. The Facility and Project sheets are filled out by 80+ agencies out in the US...I can't control how data is input into them and I can't control what version of excel it will be opened on. Thus, the max floor area always needs to be computed and I can't use the IFS function. Also, C16 on the QA sheet needs to return 7 different messages w/o using IFS--it'll remain long.

Thanks though!

[#5]

@shawb Fair enough. You own the Project and know best what can be done and what not. Glad that I could at least contribute something and appreciate your feed-back. Have nice weekend!

[#6]

@shawb 

As variant

=IFERROR(
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="No",
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$19:E$35)="No"
      ),
      "Building not conditioned",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="Yes",
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$19:E$35)="Yes"
      ),
      "Both",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="Yes",
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="no"
      ),
      "Heated Only",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="Yes",
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="No"
      ),
      "Cooled Only",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)=0,
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$19:E$35)=0
      ),
      "Identify if building cooled/heated",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)=0,
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$19:E$35)="Yes"
      ),
   "Identify if building heated",
   IF(
      AND(
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$19:E$35)=0,
         LOOKUP(2,1/(Facility!E$17:E$33=MAX($B$2:$B$14)),Facility!E$18:E$34)="Yes"
      ),
      "Identify if building cooled"
   ))))))),
   "identify building type and % of floor area")