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

[kassie]

=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" wrote:

>
> 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
>
>
> --
> hendra
> ------------------------------------------------------------------------
> hendra's Profile: http://www.excelforum.com/member.php...o&userid=35516
> View this thread: http://www.excelforum.com/showthread...hreadid=552889
>
>

[hendra]

Thanks Kassie for help and quick response.

Hendra

[Bruno Campanini]

"hendra" <[email protected]> wrote in
message news:[email protected]...
>
> 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 (edit: using same assumption as Kassie that there should be a 40% test ie there was a typo in the original post which showed two tests resulting in 30%):
=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



"hendra" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> hendra
> ------------------------------------------------------------------------
> hendra's Profile:
http://www.excelforum.com/member.php...o&userid=35516
> View this thread: http://www.excelforum.com/showthread...hreadid=552889
>

[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




"broro183" <[email protected]> wrote in
message news:[email protected]...
>
> 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...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=552889
>