Which formula will return a value for this table?
 

I created a table in Excel. If the wholesale cost A1, is between a certain
figure, B2-D2, the price will be $39.99 B3-D3. What is the correct formula?

A1
Cost $20.00
B2 C2
D2
Cost Range .01-25.00 25.01-40.00 40.01-50.00

B3 C3
D3
Retail Price 39.99 69.99 89.99


If the cost is less than $25.00, the retail price will be $39.99

If the cost is greater than $25.00, and less than $40.00, the retail price
will be $69.99

If the cost is greater than $40.00, and less than $50.00, the retail price
will be $89.99


--
AcctCA755

Which formula will return a value for this table?
 

Here's one way of doing it, without the table:

=IF(OR(A1<0.1,A150),"",LOOKUP(A1,{0.1,25,40},{39. 99,69.99,89.99}))

Hope this helps.

Pete

On Feb 21, 12:56*am, AcctCA755
wrote:
I created a table in Excel. *If the wholesale cost A1, is between a certain
figure, B2-D2, the price will be $39.99 B3-D3. *What is the correct formula? * * * * * *

* * * * * * * * * A1 * * * * * * * * * * * * * * * * * * * * * *
* * * * Cost $20.00 * *
* * * * * * * * * * * * * * * * * * * * * B2 * * * * * * * * * *C2 * * * * *
* * * * * * * * *D2 * * * * * * * * * * * * * *
* * Cost Range *.01-25.00 * * * 25.01-40.00 * * 40.01-50.00 * * * * * *

* * * * * * * * * * * * * * * * * * * * *B3 * * * * * * * * * * *C3 * * * * *
* * * * * * * * D3
* * *Retail Price * * * * * * *39.99 * * * * * 69.99 * * * * * * * * * 89.99

If the cost is less than $25.00, the retail price will be $39.99 * * * * * * * * * * * * * * * *

If the cost is greater than $25.00, and less than $40.00, the retail price
will be $69.99 * * * * * * * * * * * * *

If the cost is greater than $40.00, and less than $50.00, the retail price
will be $89.99 * * * * * * * * * * * * *

--
AcctCA755

Which formula will return a value for this table?
 

Hi,

You would set up a lookup table in the range, let's say E1:F4:

0 39.99
25 69.99
40 89.99
50

You didn't tell us what happens if the number is =50 so I left it blank.

The use the formula

=VLOOKUP(A1,E$1:F$4,2)

where the value you are checking is in A1.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AcctCA755" wrote:

I created a table in Excel. If the wholesale cost A1, is between a certain
figure, B2-D2, the price will be $39.99 B3-D3. What is the correct formula?

A1
Cost $20.00
B2 C2
D2
Cost Range .01-25.00 25.01-40.00 40.01-50.00

B3 C3
D3
Retail Price 39.99 69.99 89.99


If the cost is less than $25.00, the retail price will be $39.99

If the cost is greater than $25.00, and less than $40.00, the retail price
will be $69.99

If the cost is greater than $40.00, and less than $50.00, the retail price
will be $89.99


--
AcctCA755