Results 1 to 9 of 9

Thread: Help with If Formula with multiple arguments

  1. #1

    Help with If Formula with multiple arguments



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Can anyone help with the following


    A B
    1 SHARE PROFIT
    2 240
    3 10% OR 50%

    Note A3 is a drop down with choice of 10% or 50%

    IF THE SHARE PERCENTAGE IS 50% THEN TIMES THE PROFIT BY THE PERCENTAGE IE RESULT SHOULD BE 120
    IF THE SHARE PERCENTAGE 10% AND THE PROFIT IS NOT 0 OR LESS THAN 0 THEN SHOW 10% OF THE PROFIT, IE RESULT IS 24
    IF THE SHARE PROFIT IS 10% BUT THE PROFIT IS 0 OR LESS THAN 0 JUST SHOW PROFIT IE RESULT IS 240
    IF THE SHARE PERCENTAGE IS 50% BUT THE PROFIT IS 0 OR LESS THAN 0 THE PROFIT SHARE SHOULD BE HALVED IE IF PROFIT WAS -50 THE RESULT SHOULD BE -25
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(A3="","",IF(A3=50%,IF(B2>0,B2*A3,B2/2),IF(B2>0,B2*.1,B2)))


  3. #3

    Quote Originally Posted by NBVC View Post
    Try:

    =IF(A3="","",IF(A3=50%,IF(B2>0,B2*A3,B2/2),IF(B2>0,B2*.1,B2)))

    Brilliant thankyou

  4. #4
    Ah just noticed an problem with it ...
    When the Profit is a zero or minus and the percentage reads 10% the result should read 0

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Just change the last argument

    =IF(A3="","",IF(A3=50%,IF(B2>0,B2*A3,B2/2),IF(B2>0,B2*10%,0)))

  6. #6

    Quote Originally Posted by Bob Phillips View Post
    Just change the last argument

    =IF(A3="","",IF(A3=50%,IF(B2>0,B2*A3,B2/2),IF(B2>0,B2*10%,0)))
    Fabulous, thanks so much for your help

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    It could probably be shortened to:

    =IF(A3="","",IF(B2>0,B2*A3,IF(A3=50%,B2/2,0)))


  8. #8
    Thankyou both

    Just to complicate matters if A3 were to read 0% I want B3 to also read 0, as currently it gives 10% of B2 - would it be possible to add that into the formula you provided?

    Dave

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by faodavid View Post
    Thankyou both

    Just to complicate matters if A3 were to read 0% I want B3 to also read 0, as currently it gives 10% of B2
    Based on your latest addition to the parameters, try this in B3:

    =IF(B2= 0,0, IF(A3=50%,A3*B2, IF(A3<> 10%, 0, IF(B2< 0,0,A3*B2))))

    The logic assumes that any percentage apart from 10% and 50% will return 0.

Posting Permissions

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