Discussion Groups Home

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

6/21/2005 2:20 PM PST

Question

	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)

Was this post helpful to you?

Top

Biff

6/21/2005 8:52 PM PST

Answer

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

"SunriseCea" <SunriseCea@discussions.microsoft.com> wrote in message
news:DD7A2456-5441-4920-85D3-D37346AD637C@microsoft.com...
>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)

Was this post helpful to you?

Top

Mary

6/22/2005 1:15 PM PST

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: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
>
> "SunriseCea" <SunriseCea@discussions.microsoft.com> wrote in message
> news:DD7A2456-5441-4920-85D3-D37346AD637C@microsoft.com...
> >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)
>
>
>

Was this post helpful to you?

Top

Domenic

6/22/2005 7:51 PM PST

	In article <OR4CD2tdFHA.640@tk2msftngp13.phx.gbl>, "Biff" <biffinpitt@comcast.net> 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! :)

Was this post helpful to you?

Top

Domenic

6/22/2005 7:50 PM PST

Answer

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!

In article <DD7A2456-5441-4920-85D3-D37346AD637C@microsoft.com>,
"SunriseCea" <SunriseCea@discussions.microsoft.com> 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)

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies