| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula help - incorporate greater than & smaller than
I am trying to use the below formula in cell C7:
=IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
If you will not have any values less than zero, then one way:
=IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
Thanks Paul,
Works a treat, Am kicking myself as I type!!! "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
Correction to last formula
=IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,6,0)))))) The change involved the number six at the end. To do the formula the way you explained by testing between the two values, you could use: =IF(P7=0,1,IF(AND(P70,P7<1),IF(AND(P7=1,P7<2),3,I F(AND(P7=2,P7<3),4,IF(AND(P7=3,P7<4),5,IF(P74,6,0 )))))) Regards, Paul -- "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
One question...what if the number is equal to 4? It looks like your
attempted formula references that, but your explanation does not. If there only be number entries in P7 (or nothing), then the formula can be even simpler. =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,6))))) Regards, Paul -- "Aaron Hodson (Coversure)" wrote in message ... Thanks Paul, Works a treat, Am kicking myself as I type!!! "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
Another approach, with fewer IFs:
=IF(P7=0,1,MIN(ROUNDUP(P7+1.0000000000001,0),6)) Hope this helps. Pete On Feb 12, 3:52*pm, "PCLIVE" wrote: One question...what if the number is equal to 4? *It looks like your attempted formula references that, but your explanation does not. If there only be number entries in P7 (or nothing), then the formula can be even simpler. =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,6))))) Regards, Paul -- "Aaron Hodson (Coversure)" wrote in l... Thanks Paul, Works a treat, Am kicking myself as I type!!! "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0))))*)) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
I am trying to do a similar thing but my values are different.
The idea is that if J16 equals 0, then G11 will show 0 If J16 is greater than 0 and less than 1, then G11 will show 0 If J16 is equal to 1 but less than 2, then G11 will show 1.5 If J16 is equal to 2 but less than 3, then G11 will show 1.75 If J16 is equal to 3 but less than 4, then G11 will show 2 If J16 is equal to 4 but less than 5, then G11 will show 2.5 Can someone help me with this one?? :( "PCLIVE" wrote: Correction to last formula =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,6,0)))))) The change involved the number six at the end. To do the formula the way you explained by testing between the two values, you could use: =IF(P7=0,1,IF(AND(P70,P7<1),IF(AND(P7=1,P7<2),3,I F(AND(P7=2,P7<3),4,IF(AND(P7=3,P7<4),5,IF(P74,6,0 )))))) Regards, Paul -- "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
Thanks Paul & Pete,
Noticed when I was testing that I forgot a number! Works fine, I had a look at the 'roundup' option and it works great, but decided against for the present time, as it is easier for me to read 'if' functions as a relative beginner (this may sound odd). Thanks "PCLIVE" wrote in message ... Correction to last formula =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,6,0)))))) The change involved the number six at the end. To do the formula the way you explained by testing between the two values, you could use: =IF(P7=0,1,IF(AND(P70,P7<1),IF(AND(P7=1,P7<2),3,I F(AND(P7=2,P7<3),4,IF(AND(P7=3,P7<4),5,IF(P74,6,0 )))))) Regards, Paul -- "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
That's quite understandable, but you should be aware that there is a
limit of 7 nested functions that can be used in Excel versions before 2007. So, if you had more conditions to cover, then you would run out of IFs and you would need to consider other ways of doing it. Pete On Feb 12, 4:47*pm, "Aaron Hodson \(Coversure\)" wrote: Thanks Paul & Pete, Noticed when I was testing that I forgot a number! Works fine, I had a look at the 'roundup' option and it works great, but decided against for the present time, as it is easier for me to read 'if' functions as a relative beginner (this may sound odd). Thanks "PCLIVE" wrote in message ... Correction to last formula =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,6,0)))))) The change involved the number six at the end. To do the formula the way you explained by testing between the two values, you could use: =IF(P7=0,1,IF(AND(P70,P7<1),IF(AND(P7=1,P7<2),3,I F(AND(P7=2,P7<3),4,IF(AND*(P7=3,P7<4),5,IF(P74,6, 0)))))) Regards, Paul -- "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0))))*)) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula help - incorporate greater than & smaller than
Your first two IFs are giving the same result of zero. I'm assuming that
you meant to have it show one (1). Also, you don't have anything if it equals 5 or higher. If J16 will never be anything higher than five, and it will always be a positive number or nothing, then this should work. =IF(J16=0,0,IF(J16<1,1,IF(J16<2,1.5,IF(J16<3,1.75, IF(J16<4,2,IF(J16<5,2.5)))))) Regards, Paul -- "IF function incompetent" wrote in message ... I am trying to do a similar thing but my values are different. The idea is that if J16 equals 0, then G11 will show 0 If J16 is greater than 0 and less than 1, then G11 will show 0 If J16 is equal to 1 but less than 2, then G11 will show 1.5 If J16 is equal to 2 but less than 3, then G11 will show 1.75 If J16 is equal to 3 but less than 4, then G11 will show 2 If J16 is equal to 4 but less than 5, then G11 will show 2.5 Can someone help me with this one?? :( "PCLIVE" wrote: Correction to last formula =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,6,0)))))) The change involved the number six at the end. To do the formula the way you explained by testing between the two values, you could use: =IF(P7=0,1,IF(AND(P70,P7<1),IF(AND(P7=1,P7<2),3,I F(AND(P7=2,P7<3),4,IF(AND(P7=3,P7<4),5,IF(P74,6,0 )))))) Regards, Paul -- "PCLIVE" wrote in message ... If you will not have any values less than zero, then one way: =IF(P7=0,1,IF(P7<1,2,IF(P7<2,3,IF(P7<3,4,IF(P7<4,5 ,IF(P74,5,0)))))) HTH, Paul -- "Aaron Hodson (Coversure)" wrote in message ... I am trying to use the below formula in cell C7: =IF(P7=0,1,IF(P7=0<1,2,IF(P7=1<2,3,IF(P7=2<3,4,IF( P7=3<4,5,IF(P7=4,5,0)))))) The idea is that if P7 equals 0, then C7 will show 1 If P7 is greater than 0 and less than 1, then C7 will show 2 If P7 is equal to 1 but less than 2, then C7 will show 3 If P7 is equal to 2 but less than 3, then C7 will show 4 If P7 is equal to 3 but less than 4, then C7 will show 5 If P7 is greater than 4, then C7 will show 6 At present only '0' works in p7 showing '1' in C7 Thanks in anticipation, I am sure I will kick myself when I see the answer! Thanks Aaron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Greater than Less Than | Excel Discussion (Misc queries) | |||
| smaller formula to sum together 4 columns, 3 of them next to one a | Excel Discussion (Misc queries) | |||
| Way to Incorporate Trim Into Match/Index Formula | Excel Worksheet Functions | |||
| Font size smaller and bigger in a formula | Excel Worksheet Functions | |||
| Greater than, smaller than formula | Excel Worksheet Functions | |||
Linear Mode
