I need my SMALL formula to ignore zero's

[Cloutty]

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]

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]

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]

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]

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.

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]

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]

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}))

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

 

[Cloutty]

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}))

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

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

thanks very much, I really appreciate your help

Cloutty