Excel formulas
 

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.

Excel formulas
 

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.

Excel formulas
 

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.