Help with Max function in Averageifs formula

[eta1322]

Hi I have a averageif formula that I type in a high low criteria (example 1000 to 2000) and gives back the average

I need to be able to do the same function and give back the Max of the values but keep getting a #value back.

=IF(V7="","",AVERAGEIFS('Drill Data'!$C$6:$C$15000,'Drill Data'!$A$6:$A$15000,">="&$U7,'Drill Data'!$A$6:$A$15000,"<=" &$V7))

U=first input number
V=second input number
A=values U & V are looking up in
C=the value the average is pulling from

Thank you for any help

 

[AhoyNC]

Check the values in column A. An #VALUE error indicates that one or more cells are not numeric, but text.

 

[eta1322]

there's no issue with that it's all numbers

also I am assume if it was that then I would get an error with my averageif formula which I don't

 

[Joyner]

Until we have MAXIFS in Excel 2016...

Maybe something like array formula:

=MAX(IF(('Drill Data'!$A$6:$A$15000>=$U7)*('Drill Data'!$A$6:$A$15000<=$V7),'Drill Data'!$C$6:$C$15000))

CSE Formula - Entered with Cntl+Shift+Enter not just enter

 

[eta1322]

I tried something earlier and it didn't work, I think if forgot the "*" in there. Yours worked perfect thank you very much!

 

[Joyner]

You are welcome