Average IF Help

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi Folks,
I need to find the average,min,max and std of a
range in a one column if the value in the next column
equals 1, 2, or 3. It looks like this:

L17:L35 contains various numbers (may have spaces at the
end only). M17:M35 contains either a 1, 2 or 3(by shift).
I need the average,min,max and std values in Column L
based on the shift number in column M. Do I need to use an
offset function? Or is there an easier way.

Thanks,
Mike
 
Hi
try the following array formula (entered with
CTRL+SHIFT+ENTER):

=AVERAGE(IF(M17:M35={1,2,3},L17:L35))

Just replace AVERAGE with your other functions for min,
max, etc.
 
Frank,
Sorry if I wasn't clear- my bad. I need to average
the corresponding shift 1 onlys, shift 2 only, etc.. the
range or number of each shift changes daily, but will be
no more than 8 each. I have to manualy reset the ranges
each day. I can get the averages using countif and sumif,
but how do I get the min, max and std?



M L
207 1
210 1
215 1
213 2
207 2
209 3
218 3

Mike
 
Hi
then use the array formulas
=AVERAGE(IF(M17:M35=1,L17:L35))
or
=MAX(IF(M17:M35=1,L17:L35))
or
=MIN(IF(M17:M35=1,L17:L35))
etc.
 
Back
Top