Evaluating a range

T

TwoDot

I have 5 cells in a row. I want to average the 3 lowest numbers in those
five cells and round up to the next integer.
 
T

T. Valko

Try this:

=IF(COUNT(A1:A5)<3,"",CEILING(AVERAGE(SMALL(A1:A5,{1,2,3})),1))

Biff
 
V

Vergel Adriano

Assuming the 5 numbers are in A1:E1,

=CEILING(AVERAGE(SMALL(A1:E1,{1,2,3})), 1)
 
D

driller

Hi TwoDot,

i've seen solutions to your question and depending on the non-blank cell
values...
i'll mix Gary's and Valko's formula...

perhaps something like this..

=IF(COUNT(A1:A5)<3,"INC!",ROUNDUP((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMALL(A1:A5,3))/3,0))

short test with 3 cells containing -2.5 each....2 blank cells...
result is -3

if you want to round down the average, you can take a look at INT() function
on help files....

regards,
driller
 
D

driller

Hi TwoDot,
maybe i literally missed your "phrased" question...you want to roundup
INTEGERS....which is the reverse of INT() function....

=IF(COUNT(A1:A5)<3,"INC",IF((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMALL(A1:A5,3))/3>0,ROUNDUP((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMALL(A1:A5,3))/3,0),ROUNDDOWN((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMALL(A1:A5,3))/3,0)))

regards,
driller
 
Top