Please help me (urgent)

[hendra]

On my worksheet there's one cell (let say A1), and A1=B1/C1.
And I want to create a function in cell D1 which will return the
numbers with the following conditions :
If 0<A1<=1 then D1=70%
If 1<A1<=1.4 then D1=60%
If 1.4<A1<=2 then D1=50%
If 2<A1<=2.5 then D1=30%
If 2.5<A1<=3 then D1=30%
If A1>3 then D1=30%

Thanks so much in advance for your help!
I need it so bad.

Hendra

 

[Mallycat]

Enter the following data in a section of your spreadsheet (say J2:K7)

0 70%
1.0001 60%
1.4001 50%
2.0001 30%
2.5001 30%
3.0001 30%

put your number in cell a1
put this formula in cell b1 =VLOOKUP(A1,$J$2:$K$7,2,1)

The way it works, is the formula looks down column J and compares the
number in A1 against the first number if finds. It keeps going down
the column until it gets a number *larger *than the one in cell A1. It
the goes back up 1 row and takes the number from column K as the result.


Because you want the range to be <= to 1.4 (for example), you need a
number slightly bigger than 1.4 ie 1.4001. As described above, when
the search finds 1.4001, it then goes back and takes 60% as the value.
If there was a perfect match for 1.4, it would take the value 50% (not
what you want). You therefore need to set the 1.4001 etc to a level of
accuracy (ie decimal points) lower than the number in cell A1.

Matt

 

[Guest]

=IF(AND(0<A1,A1<=1),0.7,IF(AND(1<A1,A1<=1.4),0.6,IF(AND(1.4<A1,A1<=2),0.5,IF(AND(2<A1,A1<=2.5),0.4,IF(AND(2.5<A1,A1<=3),0.3,IF(A1>3,0.3))))))

 

[hendra]

Thanks Kassie for help and quick response.

Hendra

 

[Bruno Campanini]

On my worksheet there's one cell (let say A1), and A1=B1/C1.
And I want to create a function in cell D1 which will return the
numbers with the following conditions :
If 0<A1<=1 then D1=70%
If 1<A1<=1.4 then D1=60%
If 1.4<A1<=2 then D1=50%
If 2<A1<=2.5 then D1=30%
If 2.5<A1<=3 then D1=30%
If A1>3 then D1=30%

AND(0<A1,A1<=1) * 0.7 + AND(1<A1,A1<=1.4) * 0.6 +
AND(1.4<A1,A1<=2) * 0.5 + AND(A1>2) * 0.3

Bruno

 

[broro183]

Hi,

I realise a working solution has been provided by both Kassie & Matt
but fyi...

Kassie's solution can be just about halved in length by reversing the
order of the checks, ie deal with the largest possibilities first
rather than the smallest ones - this cuts out the need for any use of
the "And" function in this situation.

Kassie's solution:
=IF(AND(0<A1,A1<=1),0.7,IF(AND(1<A1,A1<=1.4),0.6,I
F(AND(1.4<A1,A1<=2),0.5,IF(AND(2<A1,A1<=2.5),0.4,I
F(AND(2.5<A1,A1<=3),0.3,IF(A1>3,0.3))))))

an alternative:
=IF(A1>2.5,0.3,IF(A1>2,0.4,IF(A1>1.4,0.5,(IF(A1>1,0.6,IF(A1>0,0.7,))))))

hth
Rob Brockett
Always learning & the best way to learn is to experience...

 

[hendra]

Broro183,

Yes you're right, I already modified Kassie's function.

Thanks to all of you ...

 

[CLR]

Another alternative..........

=LOOKUP(A1,{0,1.1,1.5,2.1},{"70%","60%","50%","30%"})


Vaya con Dios,
Chuck, CABGx3

 

[broro183]

Hi,

Hendra, thanks for the feedback I'm pleased we could help.

CLR, I haven't seen this before but for limited options this is tidier
than creating a separate lookup table for vlookups.
I like it

Rob Brockett
Always learning & the best way to learn is to experience...

 

[CLR]

Thanks Rob.........yeah, it is neat, and of course it's not my
original.........someone taught it to me long ago but I've only recently
started making more use of it..........it can help one over the 7-IF limit
too..........

Vaya con Dios,
Chuck, CABGx3