On 2002-05-09 11:34, klb wrote:
Correction to my previous message: the formula should read:
=(SUM((A28:A33),(A38:A39))*18.32%)+(SUM((A34:A40),(A54+A57))*14.46%)+(SUM((A51:A53),(A55:A56),(A58:A88),(A90:A132),(A148+A153+A155))*28.456%)+(SUM(A42:A50)*18.32%+SUM(A41)*6.32%+SUM(A89)*18.996%)
The sum function will ignore text in the cells it is using but the + sign will not.
The short story is: you probably have text or spaces in one of your cells.
the formula presented could further be simplified (in term of appearance) with:
=SUM(SUM(A28:A33,A38:A39)*18.32%,SUM(A34:A40,A54,A57)*14.46%,SUM(A51:A53,A55:A56,A58:A88,A90:A132,A148,A153,A155)*28.456%,SUM(A42:A50)*18.32%,SUM(A41)*6.32%,SUM(A89)*18.996%)
but I'm not sure this is the best solution for the type of problem you may have.
I think exploring the SUMIF() and SUMPRODUCT() functions may be better suited to your needs (of course I have no idea what they are).
I'm presuming that you multipliying by the percentages based on what the values in the cells. but you seem to be selecting them manually.
If I may.
if you have 10 numbers in a column:
{1;3;4;2;5;6;7;9;1;8}
and you needed to multiply anything Equal To and Less Then 4 by 3% then add these together.
everything Between 5 and 8 inclusive by 4%,
and 9 and above by 5%.
a solution like:
=(SUMIF(A1:A10,">="&4)*3%)+(SUMPRODUCT((A1:A10>=5)*(A1:A10<=8)*A1:A10)*4%)+(SUMIF(A1:A10,">="&9)*5%)
Which returns 2.66
would be much nicer.
I've rattled this one off REALLY quickly as I'm leaving work, so other will pick holes, but I will say I think it's a good starting point.
_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-05-09 12:31