If statement

[dmschave]

This is probably more complicated than my title lets on , but hopefully not.

I am looking to Autocalculate a cost based on size range but am not sure how to write the statement.

Example:
400-1500 sqft = $11/sqft
1501-2400 sqft = $10.50/sqft
2401-2900 sqft = $9.50/sqft
2901+ sqft = $8.50/sqft

So, if A1 was the sqft column and had a value of 1000 then B1 should return $11,000 (1000x$11). If A1 was 2000sqft then B1 should return $21,000 (2000x$10.50)

Something like IF(A1>=400,11*A1,IF(A1>=1501,10.5*A1,IF(A1...
I'm not sure how to use multiple IFs with ranges as a check.

Thanks
-D

[DonkeyOte]

=a1*lookup(a1,{0,400,1501,2401,2901},{0,11,10.5,9.5,8.5})

[VBA Noob]

Maybe

=LOOKUP(A1,{0,400,1501,2401,2901},{0,11,10.5,9.5,8.5})*A1

VBA Noob

[Richard Buttrey]

Hi,

It's probably easier, and certainly more flexible, to use a VLOOKUP Table.

Put the following table in a range somewhere and give the range a name - say 'Prices'

Please Login or Register  to view this content.

Now in B1 use the formula below and copy down.

Please Login or Register  to view this content.

HTH

[dmschave]

That worked! Thanks Richard.

-D

[VBA Noob]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save