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

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)

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:10,COLUMN(1: 1)),5)))-MIN(IF(IV1:INDEX(1:1,LARGE(IF(1:10,COLUMN(1:1)),5 ))0,IV1:INDEX(1:1,LARGE(IF(1:10,COLUMN(1:1)),5)) )))

Biff

"SunriseCea" wrote in message
...
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)

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

"Biff" wrote:

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:10,COLUMN(1: 1)),5)))-MIN(IF(IV1:INDEX(1:1,LARGE(IF(1:10,COLUMN(1:1)),5 ))0,IV1:INDEX(1:1,LARGE(IF(1:10,COLUMN(1:1)),5)) )))

Biff

"SunriseCea" wrote in message
...
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)

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,OF FSET(A1,0,LARGE(IF(A1:Z
10,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,OFFS ET(A1,0,LARGE(IF(1:10,
COLUMN(1:1))-CELL("col",A1),{1,2,3,4,5}))),{1,2,3,4})),"< 5 Values")

Hope this helps!

In article ,
"SunriseCea" wrote:

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)

In article ,
"Biff" wrote:

...but for some reason I'm drawn to these kind of posts!!!

Same here, Biff! This is the kind of thing I seem to enjoy most! :)