How to sum the last 5 non-blank cells in a row?

S

SunriseCea

I want to sum weekly scores where the last 5 are added then the lowest
removed. Some people have a zero value (away that day), so we need to add
the last 5 non-blank cells then deduct the minimum value (that is greater
that zero)
 
B

Biff

Hi!

Something tells me that I should avoid this post like the plague but for
some reason I'm drawn to these kind of posts!!!

Well, since you didn't provide a whole lot of detail.....

If there are less than 5 values greater than 0 the formula will return:

< 5 values

I based this on the entire range of row 1 (A1:IV1)

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(1:1,">0")<5,"< 5
values",SUM(IV1:INDEX(1:1,LARGE(IF(1:1>0,COLUMN(1:1)),5)))-MIN(IF(IV1:INDEX(1:1,LARGE(IF(1:1>0,COLUMN(1:1)),5))>0,IV1:INDEX(1:1,LARGE(IF(1:1>0,COLUMN(1:1)),5)))))

Biff
 
M

Mary

Thanks heaps - I thought of plagues when I was asked to look at it. I'll
give this a try.
Regards from New Zealand
SunriseCea
 
D

Domenic

Another way...

Assuming that the first row contains the weekly scores, try the
following formulas that need to be confirmed with CONTROL+SHIFT+ENTER....

For a set range:

=IF(COUNTIF(A1:Z1,">0")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET(A1,0,LARGE(IF(A1:Z
1>0,COLUMN(A1:Z1)-COLUMN(A1)+1)-1,{1,2,3,4,5}))),{1,2,3,4})),"< 5
Values")

For the entire row:

=IF(COUNTIF(1:1,">0")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET(A1,0,LARGE(IF(1:1>0,
COLUMN(1:1))-CELL("col",A1),{1,2,3,4,5}))),{1,2,3,4})),"< 5 Values")

Hope this helps!
 
Top