Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    If Formula (Excel 2002)

    I am try to write a formula as follows

    if H7 is "" then Check if L7 is less than C7 if it is, show "Below Min UK,else nothing, if H7, is not "" then check if K7 is below C7 if if it is then Below Min UK, else nothing

    Hope someone can help
    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    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 (Excel 2002)

    How about this?
    =IF(AND(H7<>"",OR(L7<C7,K7<C7)),"Below Min UK","")

    Steve

  3. #3
    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 (Excel 2002)

    You beat me again, though I only used 1 IF not 3 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Formula (Excel 2002)

    This is a direct translation of your description:

    <code>=IF(H7="",IF(L7<C7,"Below Min UK",""),IF(K7<C7,"Below Min UK",""))</code>

    This one is a bit shorter:

    <code>=IF(IF(H7="",L7,K7)<C7,"Below Min UK","")</code>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Formula (Excel 2002)

    I added one with 2 IFs, so Braddy can choose <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Formula (Excel 2002)

    Edited by HansV - reduced huge screenshot in size and removed useless white space. Please don't post pictures larger than 640 x 480 pixels.

    Hi Hans

    I don't know if I gave you the wrong info but the screen shot , you can see L7 Is Higher than C7 but it shows below, can you help please, got the same result with steves formula, Hope you can see what am trying to do.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Formula (Excel 2002)

    From your original question:
    <hr>if H7 is "" then Check if L7 is less than C7 if it is, show "Below Min UK,else nothing, if H7, is not "" then check if K7 is below C7 if if it is then Below Min UK, else nothing<hr>
    In your screenshot, H7 is not "", so you wanted to check if K7 is below C7, and it is. You said nothing about checking L7 if H7 is not "".

    Perhaps your description was not complete?

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Formula (Excel 2002)

    Hi

    I wil try to be a bit clearer, trouble is I have such and old head.

    The user has an option he can either put a % in H7 which creates a price in L7, which means H7 is not blank so that;s when I need to check if L7 is less than C7, or he can leave H7 Blank and put a special price in J7 if this is the case I need to check if K7 is less than C7. the reason for K7 is its just a copy of J7.

    Hope this is a bit clearer, thank you for your patience.

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    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 (Excel 2002)

    Are you looking for this??
    =IF(H7<>"",IF(L7<C7,"Below Min UK",""),IF(K7<C7,"Below Min UK",""))

    which is the opposite of what you stated originally.

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Formula (Excel 2002)

    As far as I can see, this is exactly the reverse of your original description, so exchange K7 and L7:

    <code>=IF(IF(H7="",K7,L7)<C7,"Below Min UK","")</code>

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Formula (Excel 2002)

    Hi Steve

    It's not quite right yet, as you can see H7 is blank so it should be checking K7 not L7 it should be checking L7 if H7 is not blank.
    Hope this is clear.

    Hans formula gives a similar result.

    Thanks Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Formula (Excel 2002)

    I'm completely confused now. In your screenshot, H7 is NOT blank (it contains 10.0%), so I don't understand what you mean by "as you can see H7 is blank".

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Formula (Excel 2002)

    Taking a slightly different approach, is it safe to say that the end price will either be in K7 or in L7? If the price is in K7, is L7 going to remain blank? And, likewise, if the price is in L7, does K7 remain blank? If so, you could try this formula instead:

    =IF(MAX(K7,L7)<C7,"Below Min UK","")

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Formula (Excel 2002)

    Hi Ban

    Still no luck I'm afraid if H7 is blank it still reads L7 and not K7

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Formula (Excel 2002)

    Hi Hans
    Sorry about the wrong screenshot

    I wil try to be a bit clearer

    The user has an option he can either put a % in H7 which creates a price in L7, which means H7 is not blank so that;s when I need to check if L7 is less than C7, or he can leave H7 Blank and put a special price in J7 if this is the case I need to check if K7 is less than C7. the reason for K7 is its just a copy of J7.

    Hope this is a bit clearer, thank you for your patience.

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 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
  •