Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Formulas within formulas

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Paul, Sydney Australia
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add you email to your profile to.

    Paul

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Have a good time......all the time"
    Ian Mac

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •