finding the max value from an array produced from several criterias ("MAXIFS")

[shay z]

Hi everyone,
In my attached file, you can see that with the good help of "Donkey Ote" I managed to calculate the average rating for a program from a specific channel, from a specific date and time.
Now I would like to calculate the MAX rating out of these criterias, and since there is not such a formula like MAXIFS, I have a problem.
Is there any "manipulation" that can be done?

and another question regarding this file-
can I define the name "_Data" (that fits to the database table size) to fit new number of columns and not just rows??

thanks a lot!!!!

shay

[Bob Phillips]

Try this ARRAY formula

=IFERROR(MAX(IF((INDEX(_Data,0,1)=O5)*(INDEX(_Data,0,2)>=R5)*(INDEX(_Data,0,3)<=S5),INDEX(_Data,0,MATCH(P5,INDEX(_Data,1,0),0)))),
IFERROR(MAX(IF((INDEX(_Data,0,1)=O5)*(INDEX(_Data,0,2)>=R5),INDEX(_Data,0,MATCH(P5,INDEX(_Data,1,0),0)))),
MAX(IF((INDEX(_Data,0,1)=O5)*(INDEX(_Data,0,3)=S5),INDEX(_Data,0,MATCH(P5,INDEX(_Data,1,0),0))))))

[DonkeyOte]

can I define the name "_Data" (that fits to the database table size) to fit new number of columns and not just rows??

Yes.

Name: _Data
RefersTo: =Sheet1!$A$1:INDEX(Sheet1!$1:$65536,MATCH(9.99E+307,Sheet1!$A:$A),MATCH(REPT("Z",255),Sheet1!$1:$1))

(modify INDEX range as required)

Note above text string will be last value in row 1 - if not always the case then we can modify.

[shay z]

thank you both.
this is a great site and a great forum

shay