Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding top four scores, need to insert zero
I'm using this formula to determine the top four scores of a competitions
series: =SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4")))) It works great, but not everyone has done four competitions and since those cells empty, I get a #NUM! error. Is there a way to insert zeros into the empty cells, or is there a better formula? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding top four scores, need to insert zero
Try this:
=IF(COUNT(F2:K2)<4,SUM(F2:K2),SUM(LARGE(F2:K2,{1,2 ,3,4}))) "Raza" wrote: I'm using this formula to determine the top four scores of a competitions series: =SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4")))) It works great, but not everyone has done four competitions and since those cells empty, I get a #NUM! error. Is there a way to insert zeros into the empty cells, or is there a better formula? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding top four scores, need to insert zero
Try
=SUMIF($F$2:$K$2,"="&LARGE($F$2:$K$2,4)) Hope this helps. On Mar 10, 9:26 am, Raza wrote: I'm using this formula to determine the top four scores of a competitions series: =SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4")))) It works great, but not everyone has done four competitions and since those cells empty, I get a #NUM! error. Is there a way to insert zeros into the empty cells, or is there a better formula? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fit scores | Excel Worksheet Functions | |||
Top 5 Scores | Excel Worksheet Functions | |||
Best 4 scores | Excel Discussion (Misc queries) | |||
max scores | Excel Discussion (Misc queries) | |||
Finding the top ten scores | Excel Discussion (Misc queries) |