Closest Number above and below

[robbie010]

Hi All,

a fellow forum member provided me with the following formula to identify the score closest to the average from a list of scores:

=INDEX(J2:J44,MATCH(MIN(ABS(J2:J44-J45)),ABS(J2:J44-J45),0))

Where I have 2 score equidistant from the average, 1 above and 1 below, this formula seems to prefer the lower score.......

That's not a problem, however, how can I alter this formula to choose the closest number over the average??

I.e. I have a list of scores running down column J, I use a basic formula to work out the average and put the number in cell J45. I have the formula above in cell J46 and I would like to run a similar formula in cell J47 giving me :-

A) The average in cell J45
B)The score closest to the average (below) in cell J46
C) The score closest to the average (above) in cell J47

Thanks in advance.

Simon.

[martindwilson]

=SMALL(J1:J44,COUNTIF(J1:J44,"<="&J45)+1) will find the first number above the average

[robbie010]

Martindwilson,

Please be kind...... I am a novice!

Thank you, that is exactly what I wanted, however.........

In some cases, the end result is that I have, as requested, 2 number, 1 the nearest above and 1 the nearest below the average score but I am only really interested in the one number that is the closest to the average......

Is there an amendment that can be made to the initial formula =INDEX(J2:J44,MATCH(MIN(ABS(J2:J44-J45)),ABS(J2:J44-J45),0)) that will give me the closest number, be it above or below?

If there are two numbers found i.e. one number above the average and one below the average that are equidistant, can it display both numbers, lets say the one below in cell J46 and the one above in cell J47?

Sorry for the complicated questions....

Thanks

Simon.

[martindwilson]

=IF(ABS(SMALL($J$1:$J$44,COUNTIF($J$1:$J$44,"<="&$J$45)+1)-$J$45)<=ABS(LARGE($J$1:$J$44,COUNTIF($J$1:$J$44,">="&$J$45)+1)-J45),SMALL($J$1:$J$44,COUNTIF($J$1:$J$44,"<="&$J$45)+1),"")

will give first above average if it is closest to the average or as close as the first below the average otherwise blank

=IF(ABS(SMALL($J$1:$J$44,COUNTIF($J$1:$J$44,"<="&$J$45)+1)-$J$45)>=ABS(LARGE($J$1:$J$44,COUNTIF($J$1:$J$44,">="&$J$45)+1)-$J$45),LARGE($J$1:$J$44,COUNTIF($J$1:$J$44,">="&$J$45)+1),"")

will give lowest closest to average if it is nearest or equal in difference to the one above the average else blank

[robbie010]

Hi martindwilson,

for some strange reason your formulas seemed to work perfectly fine when working on my office PC, however, when working on my laptop at home each formula works separately but as earlier, when there are two equidistant numbers above and below the average, only one number is given i.e. whilst playing with some numbers testing the formula I currently have an average of 49. within the scores there is a 46 and a 52, however, only the score of 52 is showing in cell J47 with J46 being blank?

any ideas?

[martindwilson]

are you sure they are exactly 46 and 52 and the average is exactly 49 not 49.0003 or something that you cant see because the cell is only showing a few decimal places

[robbie010]

I am using the following formula to workout the average:

=AVERAGEIF(J2:J44,">0")

If I expand the view to show decimal places then yes it is 49.xxxx?

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[robbie010]

MPGPC Handicap (Final) 3.xls

Apologies for being a pain, may as well push my luck one last time.....

To fill you in, this spreadsheet is going to be used to track the scores from my gun club. after each shoot we give prizes for the highest score before handicap, the highest score after handicap and also for Mr Average (the score closest to the average)... hence the spreadsheet. For years this has been done by hand with a pen and paper which has led to numerous mistakes

As requested, see attached spreadsheet which includes a before and after.

As per our earlier correspondence, I would like cells J46 and J47 to show the score equal to the average or the upper and lower scores closest to the average, however, it should only show both the closest upper and lower scores if they are equidistant from the average.

From this you will note that I have a Condition Formatting within column J with a basic formula to highlight the appropriate score/scores.

The final thing that I have been struggling to do is get the scores within columns N&O and Q&R to automatically sort in to ascending order. I believe that I can do this by copying these columns in to separate worksheets and setting a recorded macro button to sort them, however, if you know of a code or formula to do this automatically I will be forever indebted.....

Thanks

Simon.

[martindwilson]

there is no value greater than 42 that is the difference between 42 and 41

[Tony Valko]

Is this a duplicate thread:

http://www.excelforum.com/excel-form...m-formula.html

[robbie010]

Hi,

Not a duplicate, same person, same project, different problem!!

Martindwilson - I don't quite get what you mean!?

Would that not be 43?

Thanks

[martindwilson]

your formula refers to column j there is no 43 in col j
for column h however
the average is
29.9534883720930000000000000
not 30 so
if you had the values of 28 and 32 it would only pick 28 as that is closer than 32

[robbie010]

In Worksheet 2 - 2014 (After) - In column J, line No. 9, Chris Coniberes score is 43, line No. 10 Alan Costellos score is 41.

The average is 42, hence both scores are equidistant from the average. As such, Alan Costello's score of 41 should show in cell J46 and Chris Conibere's score of 43 should show in cell J47.

At that point, the Condition Format that I have applied to cells J2:J44 will highlight the appropriate scores so that our Shoot Secretary can easily identify the winner(s) of the Mr Average prize.

[martindwilson]

confused let me have another look

[martindwilson]

ok the average is
41.55555555555560000 not 42 change the cell format to number 10 decimal places
so 41 is closer than 43
to get the result you expect you will have to round the average
=ROUND(AVERAGEIF(J2:J44,">0"),0)