| 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) | |||
Linear Mode
