I need my SMALL formula to ignore zero's

Cloutty

unread,

Feb 16, 2011, 11:14:51 AM2/16/11

to

I have been pulling my hair out over this please help.....

=SUM(SMALL(F5:F36,{1,2,3,4,5,6,7,8,9,10}))/10 this formula takes the
ten lowest values, adds them together and divides by 10. It works fine
but, I want it to ignore zero's......

Any help gratefullt received

Cloutty

joeu2004

unread,

Feb 16, 2011, 11:26:27 AM2/16/11

to

On Feb 16, 8:14 am, Cloutty <cloutty...@gmail.com> wrote:
> I have been pulling my hair out over this please help.....
> =SUM(SMALL(F5:F36,{1,2,3,4,5,6,7,8,9,10}))/10
> this formula takes the ten lowest values, adds them together
> and divides by 10. It works fine but, I want it to ignore
> zero's.

Can't do anything about your hair :-).

But try the following array formula [*]:

=SUM(SMALL(IF(F5:F36<>0,F5:F36),{1,2,3,4,5,6,7,8,9,10}))/10

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter. Excel displays an array formula surrounded by curly
braces in the Formula Bar, i.e. {=formula}. You cannot type the curly
braces yourself. If you make a mistake, select the cell, press F2 and
edit, then press ctrl+shift+Enter.

Cloutty

unread,

Feb 16, 2011, 11:47:26 AM2/16/11

to

You sir, are a genius....;0)

thank you so much !!!

I have one issue left and then I am done, I wonder if you could help
with it .....;0)

=SUM(LARGE(I5:I36,{1,2,3,4,5,6,7,8,9,10})) this takes the larget 10
values and adds them together but, i want it to add the highest values
together even if there are only 1-10 numbers and as the number of
values exceeds ten, to then take the top ten only and add them

joeu2004

unread,

Feb 16, 2011, 5:41:12 PM2/16/11

to

On Feb 16, 8:47 am, Cloutty <cloutty...@gmail.com> wrote:
> I have one issue left and then I am done, I wonder if you
> could help with it .....;0)
>
> =SUM(LARGE(I5:I36,{1,2,3,4,5,6,7,8,9,10}))
> this takes the larget 10 values and adds them together but,
> i want it to add the highest values together even if there
> are only 1-10 numbers

You could use the following array formula (commit with ctrl+shift
+Enter instead just Enter):

=SUM(LARGE(I5:I36,ROW(INDIRECT("1:"&MIN(10,COUNT(I5:I36))))))

Although that should work, I don't like it because INDIRECT is
volatile. It would be nice to find a non-volatile alternative.

Cloutty

unread,

Feb 17, 2011, 4:46:13 AM2/17/11

to

that works great thanks very much, the solution I was trying to
achieve was to get the original formula to count blank spaces as a
zero but, this works great....;0)

thx very much

Cloutty

joeu2004

unread,

Feb 17, 2011, 6:39:59 AM2/17/11

to

On Feb 17, 1:46 am, Cloutty <cloutty...@gmail.com> wrote:
> On Feb 16, 10:41 pm, joeu2004 <joeu2...@hotmail.com> wrote:
> > You could use the following array formula (commit with

> > ctrl+shift+Enter instead just Enter):

> > =SUM(LARGE(I5:I36,ROW(INDIRECT("1:"&MIN(10,COUNT(I5:I36))))))

[....]

> that works great thanks very much, the solution I was
> trying to achieve was to get the original formula to
> count blank spaces as a zero but, this works great....;0)

Oh, that is easier, and it is better insofar as we can avoid volatile
functions.

Try the following array formula (commit with ctrl+shift+Enter instead
of just Enter):

=SUM(LARGE(IF(I5:I36="",0,I5:I36),{1,2,3,4,5,6,7,8,9,10}))

Cloutty

unread,

Feb 17, 2011, 6:52:45 AM2/17/11

to

I get an error with that formula.......???

Cloutty

unread,

Feb 17, 2011, 6:56:40 AM2/17/11

to

Sorry my error not the formula.....it works perfectly............. you
are a god...;0)

thanks very much, I really appreciate your help

Cloutty