Formulas within formulas

[Tich]

Hi, I've been using excel for a while but have been self taught, so I don't know all the lingo and in's and out's. I'm having a big problem creating a formula that's based on other formulae. Please help! I've tested each formula on their own, and they work, but I now want to add each formula together to make a total sum and it's not working. I keep getting the #VALUE! message, but it's not telling me what to change. There are a lot of brackets, etc, but I can't figure out what I'm doing wrong! Is there anybody who can help me? Do I have to type the formulae in this message to get help?

 

[Tich]

Yahoo!!! KLB gets the "You rock the nation" reward from me - THANK YOU SOOOO MUCH!

 

[Ian Mac]

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