Need help with formula

[sajohnson05]

I can not figure out my formula that I need. Below is what i am trying to do.

I have an invoice for $155K and need to split it between 162 locations based on square footage. I am trying to split by >100K, 100-300, 300-600 and <600K. I need the smaller to pay less then the larger facilities.

Any help would be great.

 

[Eric W]

You need to figure out the relative difference for the fees. For example:

	A	B	C	D	E	F	G	H	I	J	K
1					Invoice		Weightings				Shares
2	Location	Sq. Ft.	Amt.		$ 155,000.00		0	25%	6	$ 3,399.12	11.4
3	A	50	$ 3,399.12				101	50%	5	$ 6,798.25
4	B	250	$ 6,798.25				301	80%	3	$ 10,877.19
5	C	400	$ 10,877.19				601	100%	5	$ 13,596.49
6	D	700	$ 13,596.49				9999999
7	E	75	$ 3,399.12							$ 155,000.00
8	F	300	$ 6,798.25
9	G	100	$ 3,399.12
10	H	750	$ 13,596.49
11	I	299	$ 6,798.25
12	J	33	$ 3,399.12
13	K	575	$ 10,877.19
14	L	625	$ 13,596.49
15	M	800	$ 13,596.49
16	N	95	$ 3,399.12
17	O	222	$ 6,798.25
18	P	500	$ 10,877.19
19	Q	50	$ 3,399.12
20	R	950	$ 13,596.49
21	S	105	$ 6,798.25
22
23			$ 155,000.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C3 =INDEX(J2:J6,MATCH(B3,$G$2:$G$6)) I2 =COUNTIFS($B$3:$B$21,">="&G2,$B$3:$B$21,"<"&G3) J2 =$E$2/$K$2*H2 K2 =SUMPRODUCT(H2:H5,I2:I5) J7 =SUMPRODUCT(I2:I5,J2:J5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

In this layout I have the locations in column A, and the square feet in column B. In column G and H I create the weightings. In G I have the square foot ranges to create the weightings. G2 to G3-1 is the lowest range. G3 to G4-1 the next. G4 to G5-1 the next, and G5 to G6 is the largest range. I started with saying that the G5 range will have a "full" fee. The other ranges are based on that. The next smallest range will have a fee that's 80% of the top one, and so on. Then I2:I5 uses COUNTIFS to find out how many properties are in that range. Finally, the J2:J5 formulas create a weighted average for each range, based on total properties. The C3 formula is merely a lookup to get the value from the table. The J7 and C23 formulas are just to insure that everything adds up OK.

Hope this helps.

 

[sajohnson05]

Thank you that was perfect.

 

[Eric W]

Glad to help.