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

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.