|
How to
I have a work sheet that I need to check a cell to see what the value
is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 |
How to
If b3 =<20.01 then add 1
If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 In those conditions you're completely *excluding* 30 and 50. So, I'll asuume you mean: If b3 =<20.01 then add 1 If b3 20.01 and <=30.00 then add 5 If b3 30.00 and <=50.00 then add 6.75 If b3 50 then add ??? Create a 2 column table like this: ...............G..........H 3............0...........1 4.....20.02...........5 5.....30.01......6.75 6.....50.01......???? Then use this formula: =IF(COUNT(B3),B3+LOOKUP(B3,G3:G6,H3:H6),"") I'm assuming B3 will never be a negative number. -- Biff Microsoft Excel MVP wrote in message ... I have a work sheet that I need to check a cell to see what the value is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 |
How to
On Jan 21, 10:08*pm, "T. Valko" wrote:
If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 In those conditions you're completely *excluding* 30 and 50. So, I'll asuume you mean: If b3 =<20.01 then add 1 If b3 20.01 and <=30.00 then add 5 If b3 30.00 and <=50.00 then add 6.75 If b3 50 then add ??? Create a 2 column table like this: ..............G..........H 3............0...........1 4.....20.02...........5 5.....30.01......6.75 6.....50.01......???? Then use this formula: =IF(COUNT(B3),B3+LOOKUP(B3,G3:G6,H3:H6),"") I'm assuming B3 will never be a negative number. -- Biff Microsoft Excel MVP wrote in message ... I have a work sheet that I need to check a cell to see what the value is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75- Hide quoted text - - Show quoted text - Thanks Biff, it worked just like needed. I'm confused,could you tell me what the count is used for and how the greater then and less then are picked up? Here is what I ended up with, the only thing it doesn't do is anything over $5000 is 71.45 plus $5.55 for every $1000 over $5000. =IF(COUNT(C3),+LOOKUP($C3,A60:A70,B60:B70),"")+C1+ I3+F3 FYI these are UPS entery fees for shipments going into Canada that is based on the value of the shipment. 0 $0.00 20.01 $7.00 40.01 $19.45 100.01 $29.00 200.01 $40.30 350.01 $45.25 500.01 $51.45 750.01 $57.55 1000.01 $63.75 1250.01 $67.75 1600.01 $71.45 Thanks Again JW |
How to
wrote in message
... On Jan 21, 10:08 pm, "T. Valko" wrote: If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 In those conditions you're completely *excluding* 30 and 50. So, I'll asuume you mean: If b3 =<20.01 then add 1 If b3 20.01 and <=30.00 then add 5 If b3 30.00 and <=50.00 then add 6.75 If b3 50 then add ??? Create a 2 column table like this: ..............G..........H 3............0...........1 4.....20.02...........5 5.....30.01......6.75 6.....50.01......???? Then use this formula: =IF(COUNT(B3),B3+LOOKUP(B3,G3:G6,H3:H6),"") I'm assuming B3 will never be a negative number. -- Biff Microsoft Excel MVP wrote in message ... I have a work sheet that I need to check a cell to see what the value is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75- Hide quoted text - - Show quoted text - Thanks Biff, it worked just like needed. I'm confused,could you tell me what the count is used for and how the greater then and less then are picked up? Here is what I ended up with, the only thing it doesn't do is anything over $5000 is 71.45 plus $5.55 for every $1000 over $5000. =IF(COUNT(C3),+LOOKUP($C3,A60:A70,B60:B70),"")+C1+ I3+F3 FYI these are UPS entery fees for shipments going into Canada that is based on the value of the shipment. 0 $0.00 20.01 $7.00 40.01 $19.45 100.01 $29.00 200.01 $40.30 350.01 $45.25 500.01 $51.45 750.01 $57.55 1000.01 $63.75 1250.01 $67.75 1600.01 $71.45 Thanks Again JW could you tell me what the count is used for COUNT is used for an error trap. It makes sure that there is in fact a number in B3. If B3 was empty the formula would return the value that corresponds to 0 since an empty will evaluate to 0. If B3 was a text entry then it would return an error. how the greater then and less then are picked up? That's done automatically in the LOOKUP function. The function looks for the highest value that is less than or equal to the lookup_value. -- Biff Microsoft Excel MVP |
How to
On Jan 22, 10:45*pm, "T. Valko" wrote:
wrote in message ... On Jan 21, 10:08 pm, "T. Valko" wrote: If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 In those conditions you're completely *excluding* 30 and 50. So, I'll asuume you mean: If b3 =<20.01 then add 1 If b3 20.01 and <=30.00 then add 5 If b3 30.00 and <=50.00 then add 6.75 If b3 50 then add ??? Create a 2 column table like this: ..............G..........H 3............0...........1 4.....20.02...........5 5.....30.01......6.75 6.....50.01......???? Then use this formula: =IF(COUNT(B3),B3+LOOKUP(B3,G3:G6,H3:H6),"") I'm assuming B3 will never be a negative number. -- Biff Microsoft Excel MVP wrote in message ... I have a work sheet that I need to check a cell to see what the value is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75- Hide quoted text - - Show quoted text - Thanks Biff, it worked just like needed. I'm confused,could you tell me what the count is used for and how the greater then and less then are picked up? Here is what I ended up with, the only thing it doesn't do is anything over $5000 is 71.45 plus $5.55 for every $1000 over $5000. =IF(COUNT(C3),+LOOKUP($C3,A60:A70,B60:B70),"")+C1+ I3+F3 FYI these are UPS entery fees for shipments going into Canada that is based on the value of the shipment. 0 $0.00 20.01 $7.00 40.01 $19.45 100.01 $29.00 200.01 $40.30 350.01 $45.25 500.01 $51.45 750.01 $57.55 1000.01 $63.75 1250.01 $67.75 1600.01 $71.45 Thanks Again JW could you tell me what the count is used for COUNT is used for an error trap. It makes sure that there is in fact a number in B3. If B3 was empty the formula would return the value that corresponds to 0 since an empty will evaluate to 0. If B3 was a text entry then it would return an error. how the greater then and less then are picked up? That's done automatically in the LOOKUP function. The function looks for the highest value that is less than or equal to the lookup_value. -- Biff Microsoft Excel MVP- Hide quoted text - - Show quoted text - Thanks again, I really appreciate it. JW |
How to
wrote in message
... On Jan 22, 10:45 pm, "T. Valko" wrote: wrote in message ... On Jan 21, 10:08 pm, "T. Valko" wrote: If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75 In those conditions you're completely *excluding* 30 and 50. So, I'll asuume you mean: If b3 =<20.01 then add 1 If b3 20.01 and <=30.00 then add 5 If b3 30.00 and <=50.00 then add 6.75 If b3 50 then add ??? Create a 2 column table like this: ..............G..........H 3............0...........1 4.....20.02...........5 5.....30.01......6.75 6.....50.01......???? Then use this formula: =IF(COUNT(B3),B3+LOOKUP(B3,G3:G6,H3:H6),"") I'm assuming B3 will never be a negative number. -- Biff Microsoft Excel MVP wrote in message ... I have a work sheet that I need to check a cell to see what the value is and if it's between 0 and 20.01 then add 1 to the value of that cell, there are 15 amounts that I need to check. Here is a simple sample for refrence. Thanks If b3 =<20.01 then add 1 If b3 20.01 and less then 30.00 then add 5 If b3 30.00 and less then 50.00 then add 6.75- Hide quoted text - - Show quoted text - Thanks Biff, it worked just like needed. I'm confused,could you tell me what the count is used for and how the greater then and less then are picked up? Here is what I ended up with, the only thing it doesn't do is anything over $5000 is 71.45 plus $5.55 for every $1000 over $5000. =IF(COUNT(C3),+LOOKUP($C3,A60:A70,B60:B70),"")+C1+ I3+F3 FYI these are UPS entery fees for shipments going into Canada that is based on the value of the shipment. 0 $0.00 20.01 $7.00 40.01 $19.45 100.01 $29.00 200.01 $40.30 350.01 $45.25 500.01 $51.45 750.01 $57.55 1000.01 $63.75 1250.01 $67.75 1600.01 $71.45 Thanks Again JW could you tell me what the count is used for COUNT is used for an error trap. It makes sure that there is in fact a number in B3. If B3 was empty the formula would return the value that corresponds to 0 since an empty will evaluate to 0. If B3 was a text entry then it would return an error. how the greater then and less then are picked up? That's done automatically in the LOOKUP function. The function looks for the highest value that is less than or equal to the lookup_value. -- Biff Microsoft Excel MVP- Hide quoted text - - Show quoted text - Thanks again, I really appreciate it. JW You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
| All times are GMT +1. The time now is 07:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com