tonyhewson
New Member
- Joined
- Jun 30, 2014
- Messages
- 7
Im looking to find the closest match to a cell in a list.
So I have two column,
=LEFT(A5,4)&"x"&LEFT(B5,2) as the example
Frontage Depth Product '
10.7 26 10.7X26
standard products rate per metre
10.0X21 $400 per sqm
12.7 21 $430
12.7X21 $500
10.5X25 $650
10.5X28 $700
10.5X30 $545
10.5X32 $635
12.0X21 $720
12.0X25 515
12.5X21 1025
12.5X25 825
I need to take the irregular Product Type and find the closest 'Standard' Product Type. I note the multiplication of the frontage X depth while relevant is not the solution because one product could be 12m frontage depth 25 or 300sqm while another product could be 16 frontage 19 depth or 304sqm same size, different product. The primary issue is the front 10 or 12 or 14 or 16 then the depth 22 25 28 30 metres. the issue is the block designs could be 10.2X26 so its a 10mX25 product.
Each 'standard' product has a price sqm that Im will apply to the block being considered.
So block listed
10.7X26 =278,2 standard 10.5X25 = 650/sqm
Price for lot under consideration 278.2X650=$180,830
Have tried matching, the cell/block of land with list using -1 1 etcetera. what Im trying to do is find the closest match in a standard list and apply the standard per sqm rate to the size of the irregular lot....Ive tried creating an array formula but Im new to this and Ive struggled .I hope this makes sense....
TonyH
So I have two column,
=LEFT(A5,4)&"x"&LEFT(B5,2) as the example
Frontage Depth Product '
10.7 26 10.7X26
standard products rate per metre
10.0X21 $400 per sqm
12.7 21 $430
12.7X21 $500
10.5X25 $650
10.5X28 $700
10.5X30 $545
10.5X32 $635
12.0X21 $720
12.0X25 515
12.5X21 1025
12.5X25 825
I need to take the irregular Product Type and find the closest 'Standard' Product Type. I note the multiplication of the frontage X depth while relevant is not the solution because one product could be 12m frontage depth 25 or 300sqm while another product could be 16 frontage 19 depth or 304sqm same size, different product. The primary issue is the front 10 or 12 or 14 or 16 then the depth 22 25 28 30 metres. the issue is the block designs could be 10.2X26 so its a 10mX25 product.
Each 'standard' product has a price sqm that Im will apply to the block being considered.
So block listed
10.7X26 =278,2 standard 10.5X25 = 650/sqm
Price for lot under consideration 278.2X650=$180,830
Have tried matching, the cell/block of land with list using -1 1 etcetera. what Im trying to do is find the closest match in a standard list and apply the standard per sqm rate to the size of the irregular lot....Ive tried creating an array formula but Im new to this and Ive struggled .I hope this makes sense....
TonyH
Last edited: