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?

[Steve Hartman]

There are lots who can and will help you. Of course it would help if you would post what you've done so far so we can see where to start.

[EBK]

Add you email to your profile to.

Paul

[Ian Mac]

On 2002-05-08 21:08, Tich wrote:
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?

You need to explain a bit about the real problem you face, i.e. mention what is in what column/row/cells what you'd like out of the data and what the expected results might be.

if you can provide this I'm sure we can help.

[Mark W.]

On 2002-05-08 21:08, Tich wrote:
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?

Excel's Help topics contain a wealth of information. I recommend that you look at the topic...

"About multiple functions within functions, or nesting"

...and especially, note the statement, "...If a nested function does not return the correct type of value, Microsoft Excel will display a #VALUE! error value. For example, the following formula uses a nested AVERAGE function and compares it with the value 50. The comparison must return TRUE or FALSE because this is the required type of value for the first argument in an IF function."

The help topic for every worksheet function documents the data types it expects as arguments and the date type that the function returns. For example, the function, DATEVALUE, returns a serial number value (i.e., 20323) and expects as it's only argument a text representation of a valid date (e.g., "8/22/55").

Another good Excel Help topic is...

"Troubleshoot formulas and error values"

When you press the link for "What does the error #VALUE! mean?" you'll see all the possible cause of this error and a suggested action.

[ This Message was edited by: Mark W. on 2002-05-09 08:39 ]

[Tich]

Hi,

Thanks for all the replies. Here goes:

I want to add these separate formulas together to make one total formula:

=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%
=A41*6.32%
=A89*18.996%

I've looked in the Help index and either can't really understand what the issue is, or it doesn't fully explain how to add them together to make one total formula. For example, I really don't understand the whole "," (comma) concept... why can't you just use the "+"? After all, that is really the correct mathematical sign to use!

[Mark W.]

To better understand the "comma concept" see the Excel Help topic for...

"About using functions to calculate values"

Pay close attention to the explanation of "arguments".

...They're not just for married couples any more!

[klb]

Menu: Tools, Options, Calculation
Make sure the "Accept labels in formulas" is checked.

So this is the formula that I get, and I do not receive an error message in the result when that box is checked. If I uncheck it and one of the cells refered to in the formula has text in it, the #VALUE# error occurs.


=(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%+A41*6.32%)+(A89*18.996%)

[invisigirl]

Tich, try to think of it this way: When you type "SUM" in the formula, that itself means "add." So if you have a formula that says: =SUM((A1:A5),(B2:B7),(C42:C45)), you're really saying, "I want to add this, this, and this." You wouldn't, in conversation, say, "I want to add this plus this plus this," because that would be redundant.

It IS pretty confusing to think like a computer--definitely something I haven't mastered yet, either--but there is always some type of logic involved. Good luck learning the lingo! There are tons of geniuses here who can help you.

[klb]

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.