| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Evaluating a range
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Evaluating a range
=ROUNDUP((SMALL(A1:E1,1)+SMALL(A1:E1,2)+SMALL(A1:E 1,3))/3,0)
-- Gary''s Student gsnu200709 "TwoDot" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Evaluating a range
Try this:
=IF(COUNT(A1:A5)<3,"",CEILING(AVERAGE(SMALL(A1:A5, {1,2,3})),1)) Biff "TwoDot" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Evaluating a range
Assuming the 5 numbers are in A1:E1,
=CEILING(AVERAGE(SMALL(A1:E1,{1,2,3})), 1) "TwoDot" wrote: 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Evaluating a range
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 -- ***** birds of the same feather flock together.. "TwoDot" wrote: 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Evaluating a range
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))/30,ROUNDUP((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMALL(A 1:A5,3))/3,0),ROUNDDOWN((SMALL(A1:A5,1)+SMALL(A1:A5,2)+SMAL L(A1:A5,3))/3,0))) regards, driller -- ***** birds of the same feather flock together.. "driller" wrote: 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 -- ***** birds of the same feather flock together.. "TwoDot" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Evaluating One Column To Give A Answer To A Third | Excel Worksheet Functions | |||
| evaluating text cell contents | Excel Discussion (Misc queries) | |||
| Evaluating Today() function only once in a worksheet | Excel Worksheet Functions | |||
| Evaluating Rank to determine formula | Excel Worksheet Functions | |||
| Evaluating similarity of text strings | Excel Worksheet Functions | |||
Linear Mode
