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

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

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

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

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

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