Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: If a cell has a Y or N, another cell needs to make a varying calculation

  1. #1

    If a cell has a Y or N, another cell needs to make a varying calculation



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

    Hi everyone,

    I am sure this is an easy one for many of you and would appreciate any assistance, please.

    Say if cell N18 is “Y”, can I get cell P18 to look at the value in M18 and make a calculation, in this case to show 20%, so a simple M18*0.2 will do BUT if N18 is “N” the cell P18 should just be zero, or to be exact 0.00.

    I have to calculate net figures in other columns but once I have this percentage figure I can do simple cell calculations.

    Many thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IF(N18="Y",M18*0.2,0)


  3. #3
    Quote Originally Posted by NBVC View Post
    Do you mean?

    =IF(N18="Y",M18*0.2,0)
    Hi NBVC,

    Thank you very much, that is perfect.

    May I ask you one more please?

    I would like cell M3 to look at N3 and if the value in N3 is greater than 3.0, add 2 to that value in N3 and display that new value in cell M3.

    If however the value in N3 is equal to or less than 3.0 I would like to add just 1 to the value shown in N3 and display that in M3.

    Then just copy this down the column.

    Any guidance would again be appreciated.

  4. #4
    Hi NBVC,

    Also please and in going back to the original problem that you kindly solved, I am trying to use the same principle to resolve another issue.

    In effect using the same principle in your example but if “Y” to return what is in another cell. So for entry in to cell K16 I have worked that out to be =IF(G16="Y",E16) which works.

    However I need to add in/extend the above so that if cell G16 is happens to be “N”, I do not wish anything to be entered/appear in to cell K16. Is there a way that I can combine this in to the above please?

    Once again, many thanks.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try these for the above two posts

    =N3+IF(N3>3,2,1)

    =IF(G16="Y",E16,"")

  6. #6
    Hi NBVC,

    Yes both great, thank you yet again.

    Only one problem regarding the first of these two and that is that I did not think that if N3 was “0” or “0.0” it would end up being “1” when I actually want my cell to show “0.0”.

    Is there a way of saying =N3+IF(N3>3,2,1) but if N3 is “0.0” it should add nothing and therefore display “0.0”? Please.

    Many thanks

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

    =N3+IF(N3=0,0,IF(N3>3,2,1))


  8. #8
    Hi NBVC

    Thank you and yes that works, nearly there...but I seem to have lost that this is on condition that another column is Y. If that other column is N, the “0.0” is to be entered in to the cell.

    So my entry in to cell M3 say is =N3+IF(N3=0,0,IF(N3>3,2,1)) but only if P3 is “Y”, if P3 is “N”, then “0.0” gets entered in to my M3 cell.

    Finally got there…and sorry, trying to do so much here. Would you believe that I have managed to resolve so many of the queries that I have but seem to have a blank head when it come to IF scenarios in excel.

    Once again, many thanks.

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

    =IF(P3="Y",N3+IF(N3=0,0,IF(N3>3,2,1)),0)


  10. #10
    Hi NBVC,

    Just to say both “brilliant” and a really big "THANK YOU” for all of your assistance, it really has helped me.

Page 1 of 2 1 2 LastLast

Posting Permissions

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