Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Formulas within formulas

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

    Default

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

  2. #12
    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-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 ]

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
  •