Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    If Formula help please (97)

    I have a formula I need to add another scenario to.

    =IF(C20="TOIL", C9+C12+C15+C18, C9+C12+C15+C18+C21)

    To this I want to add

    If C20 is "OTP" then subtract the contents of C21 from P27

    Is this possible?

    Regards

    Kerry <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Formula help please (97)

    Try
    =IF(C20="TOIL", C9+C12+C15+C18,if(C20="OTP",P27-C21, C9+C12+C15+C18+C21))


    HTH
    Peter

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: If Formula help please (97)

    Woops - the Cell P27 is cell with a formula ( =P25+P26 ) causing a circular reference.

    I have no idea how to get around this one.

    Could the formula all be put in P27 instead?


    Kerry <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Formula help please (97)

    Sorry but without a bit more information of the layout of your sheet I have no idea of wheat you could do.
    Perhaps you could attach a copy of it, if it is not sensitive?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Formula help please (97)

    =(C20="TOIL")*(SUM(C9,C12,C15,C18))+(C20="OTP")*(S UM(P27,-C21))

    Is this what you're looking for?
    Microsoft MVP - Excel

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If Formula help please (97)

    Bat17's formula is not the cause of your circular reference, there must be some other reference loop, so you need to track it down or post it.

    If P27 isn't referenced anywhere else, you can you can move it into the formula like this condensed version:

    =IF(C20="OTP",SUM(P25,P26,-C21),(C20<>"TOIL")*C21+SUM(C9,C12,C15,C18))

    Since we don't have the whole picture, test this to see that it gives the intended result.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: If Formula help please (97)

    Thanks for all of your suggestions.

    I need time to absorb this info and give it a go.

    If I have no success I will post my spreadsheet for you to look at.


    Thanks all.

    Kerry

Posting Permissions

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