IF formula help - incorporate greater than & smaller than

[Aaron Hodson \(Coversure\)]

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

 

[PCLIVE]

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(P7>4,5,0))))))

HTH,
Paul

 

[Aaron Hodson \(Coversure\)]

Thanks Paul,
Works a treat,
Am kicking myself as I type!!!

 

[PCLIVE]

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(P7>4,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(P7>0,P7<1),IF(AND(P7=1,P7<2),3,IF(AND(P7=2,P7<3),4,IF(AND(P7=3,P7<4),5,IF(P7>4,6,0))))))

Regards,
Paul

--

 

[PCLIVE]

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


--

 

[Pete_UK]

Another approach, with fewer IFs:

=IF(P7=0,1,MIN(ROUNDUP(P7+1.0000000000001,0),6))

Hope this helps.

Pete

 

[IF function incompetent]

I am trying to do a similar thing but my values are different.

Can someone help me with this one??

 

[Aaron Hodson \(Coversure\)]

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

 

[Sandy Mann]

If there will never be a negarive numbers in P7 then another way:

=MIN(CEILING(P7,1)+1,6)

If there could be a negative number then:

=MIN(CEILING(MAX(P7,0),1)+1,6)

will give 1 or:

=IF(P7<0,"Negative Number!l",MIN(CEILING(P7,1)+1,6))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

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

 

[PCLIVE]

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


--