How to

  • Thread starter Thread starter swilson513
  • Start date Start date
S

swilson513

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
 
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
Click to expand...

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.
 
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






- Show quoted text -
Click to expand...

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
 
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






- Show quoted text -
Click to expand...

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
Click to expand...

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?
Click to expand...

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.
 
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




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.


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 -
Click to expand...

Thanks again, I really appreciate it.

JW
 
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




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.


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 -
Click to expand...

You're welcome. Thanks for the feedback!
 
Back
Top