Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If formula (WIN XP 5.1 SP1 Excel 2000)

    I am still learning Excel, so please bear with me.
    The attached xls shows in living color what I am trying to do.

    I was able to figure out the first "IF" statement and get it to work.
    I am missing something with the second one.

    Could someone please look at the attached and point me in the right direction?

    Thank you very much,
    Michael Abrams

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    Michael
    I looked at your worksheet and had difficulty following the logic of your 2nd question:

    If the sum of b9:b11 is GREATER than b4, then b9 will equal the sum of b9:b11 minus b4

    If you meant to say:
    If the sum of b9:b11 is GREATER than b4, then b12 will equal the sum of b9:b11 minus b4
    then I can help you.

    There is a longer harder formula that I will give you that could go in b12 instead of yours. (Actually I am just adding another if clause to it.)
    BUT there is a much shorter way to arrive at the same results. I will show you the long way just so you can see the use of nested if statements.
    Either of these can be place in your cell b12 to give the same results:

    Longer
    =IF(SUM(B9:B11)<B4,(B4)-SUM(B9:B11),IF(SUM(B9:B11)>B4,(SUM(B9:B11)-B4),0))

    shorter:
    =ABS(B4-SUM(B9:B11))
    This uses the ABSOLUTE function to always give you the POSITIVE difference between the two amounts
    Hope this helps

    Paul

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    Whoa - thanks Paul!! I will have to study that at work tomorrow !

    Here is a more detailed explanation of what I am looking for;
    I receive a report that lists member ADDS, TERMS and CHANGES.
    B2 is entered by me as 100 (Total reported adds)
    B3 is entered by as 100 (Total reported terms)
    B4 is entered by me as 700 (Total various types of changes)
    So this could be considered the "Summary" of the report.

    Then, lines 9,10 and 11 is the 'breakdown' of the 700 changes.

    Now here is the fun...

    Sometimes, 9 + 10 + 11 will equal less than the Total Changes. (long explanation why)
    So, what I did, was create a 4th category "Misc".
    The formula behind B12 basically says: If B9 + B10 + B11 is less than the Summary Total, then
    place the difference in B12 so it reconciles. (Total Changes Summary 700 = Total Changes Detail 700)
    That was pretty easy.

    Now here is the catch.
    Sometimes, after entering the count for B9, B10, and B11, the total equals GREATER than the Summary Total (again-
    long explanation how it occurs).
    So, because the Detail Total has to equal the Summary Total, I want to take the "Difference" from DEMO (B9).

    Using the attached as an example.
    Let's say B4 was actually 550.
    Once the entries for B9, B10 and B11 were made, B9 would become 90. (Thus B9:B11 would equal B4)

    If this doesn't make sense, maybe I need to get some shut eye!!

    I believe I did B12 correctly. If I am going about the solution totally wrong, feel free to
    tell me so !!
    Thanks again Paul. Sorry for the long winded post.

    Good Evening from CA.
    Home of Governor Terminator
    Michael

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    It appears that what you are looking for is

    1. First you enter cells B2-B4
    2. If the sum of B9-B11 does NOT equal B4 (CHANGES), THEN
    3. You want to adjust B9 (DEMO) to bring the sum of B9-B11 back to equal b4

    I have added some to your attached sheet that will get you started.
    Hope this helps

    BTW I see you have been having fun in tinsel town. Nothing like show biz to make a figure "known." Who knows? Maybe the TERMINATOR will follow Reagan to the White House !!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    If the user enters in B9 you could not put a formula to change it, you would have to have a button and macro. A "formula way" would be to LEAVE the data input in B9:b11 and add a "DATA Adj" column in Column C:
    In C9:
    <pre>=MIN(B4-SUM(B10:B11),B9)</pre>

    In C10:
    <pre>=B10</pre>

    In C11:
    <pre>=B11</pre>


    In c12:
    <pre>=B4-SUM(C9:C11)</pre>


    The you just use the adjusted values in C9:C12

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    Paul:
    Well, I gave the spreadsheet to my co-worker, and he tweaked it just right based on your suggestions.
    He got it working just as we needed.
    I'm in Sacramento, so the circus is just beginning !!!

    Steve,
    I'm an Access man at heart, and prefer using buttons and code.
    I will look at your suggestion and see what I can do.

    I reallllly appreciate you guys helping out.

    Michael

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    This will adjust it, but must be run with a button or triggered by some other event.

    <pre>Sub AdjustMe()
    Dim af As WorksheetFunction
    Set af = Application.WorksheetFunction
    If Range("b4") - af.Sum(Range("B10:B11")) < Range("B9") Then _
    Range("b9").Value = Range("b4") - af.Sum(Range("B10:B11"))
    End Sub</pre>


    The way I proposed earlier allowed you to keep what the user put into B9, the macro will overwrite it and not remember it. Also the formulas are live and will change as the input changes.

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If formula (WIN XP 5.1 SP1 Excel 2000)

    Steve - I like it !!!!!

    Much appreciated.

    Michael Abrams

Posting Permissions

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