Forum Discussion

Copper Contributor

Jan 23, 2020

Solved

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

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 ...

QA Example.xlsx216 KB

excel

Formulas and Functions

Riny_van_Eekelen
Jan 24, 2020
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.

Riny_van_Eekelen

Platinum Contributor

Jan 24, 2020

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.

shawb

Copper Contributor

Jan 24, 2020

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!

SergeiBaklan

Diamond Contributor

Jan 24, 2020

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")

Riny_van_Eekelen
Platinum Contributor
Jan 24, 2020
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!