Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
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 A13 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
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 -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=552889 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
=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(A13,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 A13 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
Thanks Kassie for help and quick response. Hendra -- hendra ------------------------------------------------------------------------ hendra's Profile: http://www.excelforum.com/member.php...o&userid=35516 View this thread: http://www.excelforum.com/showthread...hreadid=552889 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
"hendra" wrote in
message ... 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 A13 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(A12) * 0.3 Bruno |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
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(A13,0.3)))))) an alternative: =IF(A12.5,0.3,IF(A12,0.4,IF(A11.4,0.5,(IF(A11, 0.6,IF(A10,0.7,)))))) hth 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
Broro183, Yes you're right, I already modified Kassie's function. Thanks to all of you ... -- hendra ------------------------------------------------------------------------ hendra's Profile: http://www.excelforum.com/member.php...o&userid=35516 View this thread: http://www.excelforum.com/showthread...hreadid=552889 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
Another alternative..........
=LOOKUP(A1,{0,1.1,1.5,2.1},{"70%","60%","50%","30% "}) Vaya con Dios, Chuck, CABGx3 "hendra" wrote in message ... 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 A13 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me (urgent)
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT !! Auto Filter | Excel Discussion (Misc queries) | |||
URGENT !! Auto Filter | Excel Discussion (Misc queries) | |||
Excel table to Word help needed!!! Urgent. | Excel Discussion (Misc queries) | |||
"Urgent" use script to share the workbook | Excel Discussion (Misc queries) | |||
Urgent Urgent Urgent!!! | Excel Discussion (Misc queries) |