Hi -
I have a column of figures;
I have worked out how to sum, average and find the max and min values.
But I would also like to extract the number of times a number is outside a given range.
i.e.
if I have say - 3,4,6,1,7,9,12,27,26,1,2,3,4,5
I need to know how number times a number is above 9 (in this case 3 times); and also below 9 (in this case 10 times).
Hope someone can help!
Thanks,
Andy
Mac Pro, Mac OS 9.1.x, 10Gb RAM
Hi Andy,
COUNTIF is your friend.
A table with a Header Row and two Footer Rows. That way, a formula can refer to all Body Cells (B).
Formula in Footer Cell B16
=COUNTIF(B,">9")
Formula in Footer Cell B17
=COUNTIF(B,"<9")
Regards,
Ian.
Hi Andy,
COUNTIF is your friend.
A table with a Header Row and two Footer Rows. That way, a formula can refer to all Body Cells (B).
Formula in Footer Cell B16
=COUNTIF(B,">9")
Formula in Footer Cell B17
=COUNTIF(B,"<9")
Regards,
Ian.
Awesome - thanks!
One further question - wht is the formula for counting the number of cells WITHIN the range (say 5-9).
Andy
Hi Andy,
Thanks for the green tick.
formula for counting the number of cells WITHIN the range (say 5-9).
For multiple comparisons, use COUNTIFS
Formula in Footer Cell B18
=COUNTIFS(B,">5",B,"<9")
If you want greater than or equal to 5 and less than or equal to 9, formula in Footer Cell B19
=COUNTIFS(B,">=5",B,"<=9")
Rows 16-19 could be moved to another table. The formulas will automatically adjust.
Regards,
Ian.
A simple formula request (hopefully)
