Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested "IF" function in Excel 2007
How would I write a function that will count the number of tests taken and if it is 5 subtract the lowest score and then average the remaining 4? If only 4 tests then the lowest is not subtracted.
Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested "IF" function in Excel 2007
Try this:
=IF(COUNT(A1:A5)=0,"no data",AVERAGE(IF(COUNT(A1:A5)=5,LARGE(A1:A5,{1,2,3 ,4}),A1:A5))) -- Biff Microsoft Excel MVP <dale hollingsworth wrote in message ... How would I write a function that will count the number of tests taken and if it is 5 subtract the lowest score and then average the remaining 4? If only 4 tests then the lowest is not subtracted. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested "IF" function in Excel 2007
On Sun, 02 Nov 2008 18:22:43 -0800, dale hollingsworth wrote:
How would I write a function that will count the number of tests taken and if it is 5 subtract the lowest score and then average the remaining 4? If only 4 tests then the lowest is not subtracted. Thanks You did not indicate what you want to happen if there are other than 4 or 5 tests, but to average the highest four scores: =AVERAGE(LARGE(rng,{1,2,3,4})) where rng is a reference to your five potential cells. This formula will return an error if there are not at least four scores, and will average the highest four no matter how many scores are present. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluate "nested-merge" function? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Need function that will work in Excel 2003 like "Countifs" in 2007 | Excel Worksheet Functions | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
How do I find the "case" function in Excel 2007 ? | Excel Discussion (Misc queries) |