IF THEN or VLOOKUP??

[archi_bum]

http://www.flickr.com/photos/79573271@N03/12418973234/
http://www.flickr.com/photos/79573271@N03/12418973114/

Hello.

I am in need of writing a seemingly easy formula. I've attached two screen shots that should make sense of my request. Both spreadsheets are within the same Excel 2013 file but are on different tabs.

I would like for the "EXTENDED PRICE" cell to be automatically populated based upon the value in the "SQUARE FOOTAGE" cell. The "SQUARE FOOTAGE" cell is an absolute value. However, the "EXTENDED PRICE" cell should reflect a dollar amount determined by where the absolute "SQUARE FOOTAGE" value falls within a square footage range (on another tab).

If the actual "SQUARE FOOTAGE" is 645 SF (=<700 SF) then the "EXTENDED PRICE" is $15.00. Whereas if the actual "SQUARE FOOTAGE" is 2582 SF (2500-2800) then the "EXTENDED PRICE" is 11.50.


Thank you in advance for your assistance!



SORRY I'M HAVING TROUBLE ADDING SCREEN SHOTS - HERE ARE LINKS TO THEM ON FLICKR.

 

[cyrilbrd]

Hi, Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="class: grid, width: 325"]
<tbody>[TR]
[TD="width: 65"]SF
[/TD]
[TD="width: 65"]$/SF[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"]actual[/TD]
[TD="width: 65"]Ex_price[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]701[/TD]
[TD="align: right"]14.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1001[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1301[/TD]
[TD="align: right"]13.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1601[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1901[/TD]
[TD="align: right"]12.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2201[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2501[/TD]
[TD="align: right"]11.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2801[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3101[/TD]
[TD="align: right"]10.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3401[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Formula in E1 is =INDEX($B$2:$B$12,MATCH(D2,$A$2:$A$12,1))
Would that work for you?

 

[ChiChiR]

Hello!

The only thing I can think to use is a SUMIFS, and for that a few helper columns will make it easier. My Sheet1 has the following setup, starting in cell A1:

SF $/SF
<700 $15.00 0 700
701-1000 $14.50 701 1000
1001-1300 $14.00 1001 1300
1301-1600 $13.50 1301 1600
1601-1900 $13.00 1601 1900
1901-2200 $12.50 1901 2200
2201-2500 $12.00 2201 2500
2501-2800 $11.50 2501 2800
2801-3100 $11.00 2801 3100
3101-3400 $10.50 3101 3400
3401-3700 $10.00 3401 3700

Columns C and D are my helpers, containing the low and high ends of each of your ranges. On my second sheet, the EXTENDED PRICE formula is in B2, and the SQUARE FOOTAGE value is in A2. Here's my formula:

=SUMIFS(Sheet1!$B$2:$B$12,Sheet1!$C$2:$C$12,"<="&$A2,Sheet1!$D$2:$D$12,">="&$A2)

Hope this helps!
Cece

 

[archi_bum]

SOLVED!!

Thank you both for your insight, and special thank you to ChiChiR for providing the perfect solution to my particular issue.