Help!!

[meladramatic]

Hi, I am brand new to this forum and not sure where to post this so forgive me if i did it wrong. I need help with a formula for a rate card for my job. I need Excel to calculate dollar amounts based on footages. The criteria are listed below. I have tried everything I know based on my limited knowledge. Any help you folks can offer is unbelievably appreciated.

0' to 675'=$70.00
676' to 1320'=$0.11 per foot
1321' to 5280'=$0.07 per foot
5218' and up=$0.05 per foot

 

[Sam_ExcelVault]

There are certainly a few ways to go around doing this but I’ll suggest a formula as per your post request.

Supposing you have the footage value in cell A1, this formula should calculate the price based on that footage.

IF(AND(A1>0,A1<=675),70,IF(AND(A1>=676,A1<=1320),A1*0.11,IF(AND(A1>=1321,A1<=5280),A1*0.07,IF(A1>5280,A1*0.05,0))))

The calculated price will be in whichever cell you have this formula.

 

[jtakw]

Hi,

Here are two ways based on my sample:

F2 formula has your requirements "hard coded" into the formula.
G2 formula uses a Table with your requirements which makes it easier in the future in case your requirements change.

Book1
	A	B	C	D	E	F	G
1	From	To	Cost		Footage	Total Cost	Total Cost
2	0	675	$70.00
3	676	1320	$0.11		0	$70.00	$70.00
4	1321	5280	$0.07		380	$70.00	$70.00
5	5281		$0.05		675	$70.00	$70.00
6					676	$70.11	$70.11
7					1000	$105.75	$105.75
8					1320	$140.95	$140.95
9					1400	$146.55	$146.55
10					5000	$398.55	$398.55
11					5280	$418.15	$418.15
12					6000	$454.15	$454.15
Sheet1

Cell Formulas
Range	Formula
F2	=IF(E2="","",IF(AND(E2>=0,E2<=675),70,SUM(70,IF(E2>5280,(E2-5280)*0.05+348.15,IF(E2>1320,MIN(E2-1320,5280-1320)*0.07+70.95,(E2-675)*0.11)))))
G2	=IF(E2="","",IF(AND(E2>=A$2,E2<=B$2),C$2,SUM(C$2,IF(E2>B$4,(E2-B$4)*C$5+(B$4-B$3)*C$4+(B$3-B$2)*C$3,IF(E2>B$3,MIN(E2-B$3,B$4-B$3)*C$4+(B$3-B$2)*C$3,(E2-B$2)*C$3)))))

Either way, F2, G2 formulae copied down.
Change the 0 in F2 formula and/or Table to 1 if you didn't mean 0, cause the cost is $70.00 even when nothing is purchased or sold.

@Sam, your formula does not account for the Tiered Cost.