Index Match for closest value with multiple conditions

[abschy]

Hi all,

I'm having issues coming up with a formula that gives an approximate match value based on multiple conditions..

My data:
- Column A = Region
- Column D = Brand
- Column F = Size
- Column J = Cost psm

So far, i have this formula that works to give the average cost psm of a project in a certain region.
- B4 contains the region, C7 contains the brand i want and C8 contains the size i want to estimate for

=IFERROR(ROUNDUP(SUMIFS(Table3[COST PSM],Table3[Region],'Formula Testing'!$B$4,Table3[Brand],'Formula Testing'!C$7,Table3[COST PSM],">0")/COUNTIFS(Table3[Region],'Formula Testing'!$B$4,Table3[Brand],'Formula Testing'!C$7,Table3[COST PSM],">0")*'Formula Testing'!C$8,-2),"")

I now need to improve the formula to give me an approximate average cost psm of a certain project in a certain region based on size.

For example,
Brand A in SEA has 10 projects, all with differing sizes - eg 0.2 sqm to 50 sqm, and if i want the average cost psm of a size 7 sqm, in the above equation, i would like for the SUMIFs portion of the equation to return the sum of costs for projects which are the closest to 7sqm.

So in this example, i only want the average cost psm of the 2 red numbers.


Would this be possible?

Any help is appreciated!

Thank you!!

Best,
Abigail

 

[KRice]

Try this...

This assumes that there will be enough data to bound the target size, otherwise the 2-point linear interpolation for cost estimation will fail.

Book1
	A	B	C	D	E	F	G	H
1	Fill yellow cells with desired parameters				Brand	Region	Size/sqm	Cost PSM
2					A	China	40	5.00
3					A	China	31	4.00
4	Region -->	China			A	SEA	4	30.00
5					A	SEA	52	4.00
6					A	SEA	9	20.00
7		Brand -->	C		B	China	1	32.00
8		Size -->	15.5		B	SEA	19	6.00
9					C	China	29	2.00
10	Cost Interpolation				C	China	0.8	51.00
11		Size	Cost
12	largest below	0.8	51
13	smallest above	29	2
14		15.5	25.45745	<-- est. based on linear interpolation
Sheet7

Cell Formulas
Range	Formula
B12	B12	=MAX(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)<=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
C12	C12	=MAX(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)<=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($H$2:$H$10)))
B13	B13	=MIN(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)>=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
C13	C13	=MIN(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)>=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($H$2:$H$10)))
B14	B14	=C8
C14	C14	=FORECAST(B14,C12:C13,B12:B13)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

Sorry, but I discovered an error where C12:C13 weren't returning the correct value. I've incorporated a work-around in this solution and added some improvements and messaging to the final cost interpolation formula to cover cases where the target combination is represented exactly in the table and where there is only one data point.

Book1
	A	B	C	D	E	F	G	H
1	Fill yellow cells with desired parameters				Brand	Region	Size/sqm	Cost PSM
2					A	China	40	5.00
3					A	China	31	4.00
4	Region -->	China			A	SEA	4	30.00
5					A	SEA	52	4.00
6					A	SEA	9	20.00
7		Brand -->	A		B	China	1	32.00
8		Size -->	30		B	SEA	19	6.00
9					C	China	29	2.00
10	Cost Interpolation				C	China	0.8	51.00
11		Size/sqm	Cost PSM
12	largest size below	0	0.00
13	smallest size above	31	4.00
14		30	only 1 pt	<-- est. based on linear interpolation
Sheet7

Cell Formulas
Range	Formula
B12	B12	=MAX(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)<=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
C12:C13	C12	=SUMPRODUCT(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10=B12)*($H$2:$H$10))
B13	B13	=MIN(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)>=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
B14	B14	=C8
C14	C14	=IF(B14=B13,C13,IF(OR(B12=0,B13=0),"only 1 pt",FORECAST(B14,C12:C13,B12:B13)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

I'll try once again...I discovered an issue with the last improved version when/if there are duplicates in the data table. Cells C12:C13 would still return an incorrect answer if the duplicates were involved in the cost calculation. Please disregard the earlier versions and consider this one. Also note in the cell formula section that the array formulas found in B12, B13, C12, and C13 will need special treatment...click on each, hit F2, then CTRL-SHIFT-ENTER

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H
1	Fill yellow cells with desired parameters				Brand	Region	Size/sqm	Cost PSM
2					A	China	40	5.00
3					A	China	31	4.00
4	Region -->	SEA			A	SEA	4	30.00
5					A	SEA	52	4.00
6					A	SEA	9	20.00
7		Brand -->	A		B	China	1	32.00
8		Size -->	12.5		B	SEA	19	6.00
9					C	China	29	2.00
10	Cost Interpolation				C	China	0.8	51.00
11		Size/sqm	Cost PSM
12	largest size below	9	20.00
13	smallest size above	52	4.00
14		12.5	18.70	<-- est. based on linear interpolation
15
Sheet7

Cell Formulas
Range	Formula
B12	B12	=MAX(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)<=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
C12:C13	C12	=MAX(--($E$2:$E$10=$C$7)*--($F$2:$F$10=$B$4)*--($G$2:$G$10=B12)*($H$2:$H$10))
B13	B13	=MIN(IF(($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)>=$C$8,($E$2:$E$10=$C$7)*($F$2:$F$10=$B$4)*($G$2:$G$10)))
B14	B14	=C8
C14	C14	=IF(B14=B13,C13,IF(OR(B12=0,B13=0),"only 1 pt",FORECAST(B14,C12:C13,B12:B13)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.