Which formula to pick up a value

[DenisLafrance]

Hi everyone,

I'm looking for a formula that will pick the closest value to the average from a list of values.  The value should also not be less than the average calculated from the list of values.

As example, I have the following values

23.29; 21.63; 21.84; 21.58; 23.70; 22.71

the average is 22.46 and the value returned by the formula should be 22.71

Thanks in advance

[#2]

@DenisLafrance  I believe this should work:

=XLOOKUP(AVERAGE(A1:F1),A1:F1,A1:F1,,1)

where the numbers are in the A1:F1 range. 

I originally used SORT() inside a lookup function but then realized this XLOOKUP() worked without the SORT().

[#3]

@mtarler 

in fact, the data are in a column, not in a row...

[#4]

@DenisLafrance  Data usually is, but that doesn't change the fact that you have to replace A1:F1 with the actual range you will be using.  Since you didn't provide a sample sheet, I just copied your example text into a sheet and by default it parsed it as a row. I didn't bother switching it to a column because I didn't know what your range is regardless.

But more importantly, did it work for you?  Will you mark it as a solution or is there an issue/question?

[#5]

@DenisLafrance 

My formula is a little more complicated

= XLOOKUP( 0, ABS( data - AVERAGE(data) ), data, , 1 )

The formula is intended to take into account the fact that the closest value may be above the average

= XLOOKUP( AVERAGE(data), data, data, , 1 )

or below it

= XLOOKUP( AVERAGE(data), data, data, , -1 )

p.s. I defined "data" to be the array constant

={23.29;21.63;21.84;21.58;23.7;22.71}

[#6]

@Peter Bartholomew 

in fact, the data are in a column, not in a row.  What is the impact on your formula

Thanks

[#7 · Accepted solution]

@DenisLafrance 

Absolutely no difference whatsoever.  I added the braces to your formula and let the defined name 'data' refer to it.  This gives a named array constant which, given that I am in an English locale is actually a column array.  Not that it matters in the slightest.  The average of a set of numbers is the same presented as a row or a column and XLOOKUP works equally well on rows or columns, unlike VLOOKUP.

If your data is held as a column range, that is fine, just give it a name ('data' if you con think of nothing more descriptive) so that you can reference it by name.