IF Statement

stevehorton09 · Jan 24th 2006

Hi all,

Having a mental block at the moment...

Its an IF statement with figure that falls into one of the attachment points (below). From this the factor is given and the cell below should show the relevant factor

I.e in a1 = 112 and therefore attachment of 100-124, which means in cell a2=2.778
Attachment Factor
>150 1.000
125 - 149 1.667
100 - 124 2.778
75 - 99 4.630
50 -74 7.716
25 - 49 12.860

I have attached a worksheet with just the table in and a1 filled in.

If you can have a look that would be great, im not so great with between figures!

**Krishnakumar** · Jan 24th 2006

Re: IF Statement

Hi,

In A2,

=LOOKUP(A1,{0,0;24,0;25,12.86;50,7.716;75,4.63;100,2.778;125,1.667;150,1})

HTH

johnccc · Jan 24th 2006

Re: IF Statement

Hi,

Or you could write a user defined function and use Case Select

John

stevehorton09 · Jan 24th 2006

Re: IF Statement

Thanks guys,

I attach another way which I would prefer to use but think the nested if is limited?

As you can see, cell F18 is set at a 100 and the if staement works but I can not add anymore to the IF statement!! i.e attachment 125-149 and 150+

Please find attached.

adamsapple · Jan 24th 2006

Re: IF Statement

Try using the "and" statement:

ie. if and(a1<75, a1>50),7.716,if(and(a1<100,a1>49),4.63, etc...

This is not the most efficient way of doing it though, because if you change your limits or your factors you have to edit a long formula. Doing it as a lookup means you can just edit the table.

ismail · Jan 24th 2006

Re: IF Statement

Hi Krish
Can we use a simple vlookup. pls have a look the attached file.

stevehorton09 · Jan 24th 2006

Re: IF Statement

Thanks very guys,

really appreciate it

IF Statement

Participate now!

Share