looking for the closest price to a reference price

[PeterO'Connor]

Hi,

I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?

I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.

I would also like to reference the source on the next column.

Perhaps you can help?

Thanks very much,

Peter

[JeanRage]

Hi Peter,

You might be facing a case of combining array formulas both with match(max( ... for negative values and match(min( ... for positive values ...

HTH

[DonkeyOte]

The Array you outline should work... in essence it matches the ABS variance of each data point (from target value) to the MIN ABS variance for all data points and returns the associated value.

Presumably you're entering this as an Array - ie with CTRL + SHIFT + ENTER rather than Enter as normal - once Array is set the formula will become encased within { }

A sample workbook would help - remove / dummy anything confidential.

[PeterO'Connor]

Hey JeanRage/DonkeyOte,

Thanks for the swift replies.

I am following your logic, but the formulas seems to have limitations.

I'm attaching a sample for you.
- I have remarked in a couple of instances where the formulae fail.

Thanks again,

Peter

[DonkeyOte]

You need to account for the blanks - these will otherwise be coerced to 0 and 0 is closest value to target, eg:

B4:
=INDEX($E4:$O4,MATCH(MIN(IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4))),IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4),""),0))
confirmed with CTRL + SHIFT + ENTER
copied down

EDIT:
If preferred - to avoid errors from displaying - precede the above with a COUNT test of E:O to ensure at least one entry before conducting the Array

For returning the value from row 2 - assuming they may change (ie Position less 1 not necessarily valid)

=IF(ISNA($B4),"",INDEX($E$2:$O$2,MATCH($B4,$E4:$O4,0)))

(if you opt for COUNT test to prevent NA (displaying Null instead) then modify ISNA to ="" test)

[PeterO'Connor]

Thanks DonkeyOte.

The real problem is that am looking up the other price sources from elsewhere. This creates a host of problems. To avoid zero values, I have been using if(iserror(lookup)," ",lookup).

So basically, I need to find the closest match given that some cells are blank or have " ". This has caused error values to come back regardless of whether I use the suggested countif function as a workaround.

Any ideas?

Thanks again,

Peter

[DonkeyOte]

I need to find the closest match given that some cells are blank or have " ". This has caused error values to come back regardless of whether I use the suggested countif function as a workaround.

I'm afraid I don't understand Peter.

The suggested Array validates on an ISNUMBER basis so whether your data set includes spaces or blanks amongst the numerics is irrelevant - they're simply ignored.

The pre-emptive COUNT test was merely to demonstrate that you can limit the times the suggested Array is invoked by validating as to whether or not any numerics exist in the range to start with - if not there's no need to validate, eg:

B4:
=IF(COUNT($E4:$O4),INDEX($E4:$O4,MATCH(MIN(IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4))),IF(ISNUMBER($E4:$O4),ABS($E4:$O4-$A4),""),0)),"")
confirmed with CTRL + SHIFT + ENTER
copied down

I'm afraid at this point I think you need to post a sample which demonstrates the above returning erroneous results.

[PeterO'Connor]

Sorry to confuse.


The thing is;
when a lookup returns a blank or " ", it renders "=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0))" ineffective, regardless of whether there are other values across the row - I just get an error. The result is that I have to filter and delete blanks to get it to work in a reasonable way.

Next problem:
In the previously attached example, cell B4, C4 and D4 function properly but if change F4 to 101, C4 and D4 cease to function properly. This might be related to the fact that it is in excess of 100. Further, if you change to F4 to -101, cells B4, C4, D4 fail to work.

Do you think I need to use VBA code to get around these issues or is there a formula that you are aware of that would solve it?

Thanks again for your help.

Peter

[DonkeyOte]

The thing is;
when a lookup returns a blank or " ", it renders "=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0))" ineffective, regardless of whether there are other values across the row - I just get an error.

correct, but the above formula was not the formula I suggested you use (for that reason).

Next problem:
In the previously attached example, cell B4, C4 and D4 function properly but if change F4 to 101, C4 and D4 cease to function properly. This might be related to the fact that it is in excess of 100. Further, if you change to F4 to -101, cells B4, C4, D4 fail to work.

Let's revisit the above once you've tested the formula suggested in my prior post.

[PeterO'Connor]

I have used that but have found that things only function when the value is less than Primary Price.

What I found was: even where there are three prices available, one lookup returning blank causes and error.

[DonkeyOte]

I don't mean to be a bore nor sound like a broken record but please post a sample file with the prior suggestion in place which illustrates the errors you're experiencing ... I for one can not replicate.

I have used that but have found that things only function when the value is less than Primary Price.

Using your earlier file, with the suggested formula in B4 and copied down to B22, I get:

99.0000
105.0000
5.0000
[Null * 6]
(1.3000)
[Null * 9]

The 2nd & 3rd results are both > primary price (99.88, -0.21 respectively) which goes against your assertion above.