[Gifford Mom]

I have a worksheet that we would like to use to calculate price + labor for
work. The price formula has me stumped. I would like to be able to put in the
price in one box and have the formula determine the amount at which to
multiply it by.

For example:

Price = 20.00 then the formula would determine how many to multiply it by
(3.25) from the range given below.

Range is 0-2.00 *4
2.01-5 *3.75
5.01-10 *3.5
10.01-20.00 *3.25
20.01-40 *3
40.01-60 *2.75
60.01-100 *2.5
100.01-150 *2.25
150.01 *2

I have the labor formula and then the total is a sum formula. Please help
with the price formula. Thank you.

[Jacob Skaria]

With price in cell A1 try the below formula in cell B1

=LOOKUP(A1,{0,2.01,5.01,10.01,20.01,40.01,60.01,10 0.01,150.01},{4,3.75,3.5,3.25,3,2.75,2.5,2.25,2})

'handle blank entries in cell A1
=IF(A1="","",LOOKUP(A1,{0,2.01,5.01,10.01,20.01,40 .01,60.01,100.01,150.01},{4,3.75,3.5,3.25,3,2.75,2 .5,2.25,2}))

--
Jacob


"Gifford Mom" wrote:

I have a worksheet that we would like to use to calculate price + labor for
work. The price formula has me stumped. I would like to be able to put in the
price in one box and have the formula determine the amount at which to
multiply it by.

For example:

Price = 20.00 then the formula would determine how many to multiply it by
(3.25) from the range given below.

Range is 0-2.00 *4
2.01-5 *3.75
5.01-10 *3.5
10.01-20.00 *3.25
20.01-40 *3
40.01-60 *2.75
60.01-100 *2.5
100.01-150 *2.25
150.01 *2

I have the labor formula and then the total is a sum formula. Please help
with the price formula. Thank you.

[Jacob Skaria]

To make the formula shorter you can type in the range of values in another
sheet say Sheet2 as below and try the below formulas

Col A Col B
0 4
2.01 3.75
5.01 3.5
10.01 3.25
20.01 3
40.01 2.75
60.01 2.5
100.01 2.25
150.01 2

using LOOKUP()
=IF(A1="","",LOOKUP(A1,Sheet2!A1:A9,Sheet2!B1:B9))

OR using VLOOKUP()
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:B9,2,1))

--
Jacob


"Jacob Skaria" wrote:

With price in cell A1 try the below formula in cell B1

=LOOKUP(A1,{0,2.01,5.01,10.01,20.01,40.01,60.01,10 0.01,150.01},{4,3.75,3.5,3.25,3,2.75,2.5,2.25,2})

'handle blank entries in cell A1
=IF(A1="","",LOOKUP(A1,{0,2.01,5.01,10.01,20.01,40 .01,60.01,100.01,150.01},{4,3.75,3.5,3.25,3,2.75,2 .5,2.25,2}))

--
Jacob


"Gifford Mom" wrote:

I have a worksheet that we would like to use to calculate price + labor for
work. The price formula has me stumped. I would like to be able to put in the
price in one box and have the formula determine the amount at which to
multiply it by.

For example:

Price = 20.00 then the formula would determine how many to multiply it by
(3.25) from the range given below.

Range is 0-2.00 *4
2.01-5 *3.75
5.01-10 *3.5
10.01-20.00 *3.25
20.01-40 *3
40.01-60 *2.75
60.01-100 *2.5
100.01-150 *2.25
150.01 *2

I have the labor formula and then the total is a sum formula. Please help
with the price formula. Thank you.