IF formula help - incorporate greater than & smaller than

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aaron Hodson (Coversure) - 12 Feb 2008 15:25 GMT

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 to this Message

PCLIVE - 12 Feb 2008 15:36 GMT

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

>I am trying to use the below formula in cell C7:
>
[quoted text clipped - 16 lines]
>
> Aaron

Reply to this Message

Aaron Hodson (Coversure) - 12 Feb 2008 15:41 GMT

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

> If you will not have any values less than zero, then one way:
>
[quoted text clipped - 23 lines]
>>
>> Aaron

Reply to this Message

PCLIVE - 12 Feb 2008 15:52 GMT

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

> Thanks Paul,
> Works a treat,
[quoted text clipped - 27 lines]
>>>
>>> Aaron

Reply to this Message

Pete_UK - 12 Feb 2008 16:35 GMT

Another approach, with fewer IFs:

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

Hope this helps.

Pete

> One question...what if the number is equal to 4? It looks like your
> attempted formula references that, but your explanation does not.
[quoted text clipped - 44 lines]
>
> - Show quoted text -

Reply to this Message

PCLIVE - 12 Feb 2008 15:43 GMT

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

> If you will not have any values less than zero, then one way:
>
[quoted text clipped - 23 lines]
>>
>> Aaron

Reply to this Message

IF function incompetent - 12 Feb 2008 16:46 GMT

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?? :(

> 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))))))
[quoted text clipped - 35 lines]
> >>
> >> Aaron

Reply to this Message

PCLIVE - 12 Feb 2008 17:06 GMT

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

>I am trying to do a similar thing but my values are different.
>
[quoted text clipped - 47 lines]
>> >>
>> >> Aaron

Reply to this Message

Aaron Hodson (Coversure) - 12 Feb 2008 16:47 GMT

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

> 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))))))
[quoted text clipped - 35 lines]
>>>
>>> Aaron

Reply to this Message

Pete_UK - 12 Feb 2008 17:05 GMT

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\)"
<aa...@coversure.co.uk> wrote:

> Thanks Paul & Pete,
> Noticed when I was testing that I forgot a number!
[quoted text clipped - 50 lines]
>
> - Show quoted text -

Reply to this Message

Sandy Mann - 12 Feb 2008 16:50 GMT

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))

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I am trying to use the below formula in cell C7:
>
[quoted text clipped - 16 lines]
>
> Aaron

Reply to this Message