Results 1 to 15 of 18
Thread: If Formula (Excel 2002)

20041112, 12:56 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20041112, 13:05 #2
 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

20041112, 13:05 #3
 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

20041112, 13:06 #4
 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>

20041112, 13:07 #5
 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>

20041112, 13:32 #6
 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
BraddyIf you are a fool at forty, you will always be a fool

20041112, 13:36 #7
 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?

20041112, 14:02 #8
 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.
BraddyIf you are a fool at forty, you will always be a fool

20041112, 14:28 #9
 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

20041112, 14:28 #10
 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>

20041112, 14:57 #11
 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 BraddyIf you are a fool at forty, you will always be a fool

20041112, 15:02 #12
 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".

20041112, 15:14 #13
 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","")

20041112, 15:41 #14
 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
BraddyIf you are a fool at forty, you will always be a fool

20041112, 16:33 #15
 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.
BraddyIf you are a fool at forty, you will always be a fool