# determine closest entry to given cell

#### WLHagen

##### Board Regular
For all: Just a friendly internal contest at my work this week - guessing an ending balance of our month-end accounts for this month.

Trying to determine who is the closest guess to the actual balance. My Excel 2010 spreadsheet has the following items, headers in row 2, and information below that:

Column A: Employee Name
Column B: Employee's Guess
Column C: Absolute difference between their guess and the actual.

I've Conditionally formatted column C to highlight the "bottom 1", and tested it to make sure that it highlights both items if there is a tie between the "over by" and "under by" the exact same amount. I have a named range-ActualBalance - for the actual ending balance.

I was hoping someone here could assist me with a formula in a cell that would automatically populate the cell with the winner(s), so I don't have to glance down the list manually.

Thanks in advance for all input, and have a wonderful Easter weekend!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi,

If I understand you correctly, does something like this work?....

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheck**ActualEmployee Name(s)*
2Bill9811*199Bill*
3Cath9544***Ben*
4Ben100-11***Kath*
5Kath9811***Sue*
6Bob9099*****
7Sue100-11*****
8*********
Sheet1

** You will need to change your cell references to suit your layout.**

The formula in H2 needs entering with ctrl shift enter NOT just enter, you can then copy it down.
The rest of teh formulas need to be copied down.

I hope that helps.

Ak

Hi,

On second thoughts........

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheckActualEmployee Name(s)
2Bill2119193740Cath
3Cath33737
4Ben3288
5Kath251515
6Bob93131
7Sue112929
8
Sheet1

Ak

Last edited:
Hi,

On third thoughts!!........

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheckActualEmployee Name(s)
2Bill21-11120Bill
3Cath1911Cath
4Ben32-1212
5Kath25-55
6Bob91111
7Sue1199
8
Sheet1

Ak

Thanks for all the good thoughts

Using your spreadsheet above, what I was looking for was to have text in cell H1 that says "And the winner(s) is/are:" Then a formula in H2 that shows the winners names pulled from Column A that were the winners (Bill, Ben, Kath, Sue) from example 1,(Ben) from Example 2, and (Bill, Cath) from Example 3.

Hi,

Did you mean something like this?....

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheckActualAnd the winners are:
2Bill21-11120Bill
3Cath1911Cath
4Ben32-1212
5Kath25-55
6Bob91111
7Sue1199
8
Sheet1

******

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheckActualAnd the winners are:
2Bill9811199Bill
3Cath9544Ben
4Ben100-11Kath
5Kath9811Sue
6Bob9099
7Sue100-11
8
Sheet1

*****

Excel Workbook
ABCDEFGHI
1Employee NameEmployee's GuessDifferenceCheckActualAnd the winner is:
2Bill211919840Ben
3Cath33737
4Ben3288
5Kath251515
6Bob93131
7Sue112929
8
Sheet1

I hope that's what you require.

Ak

Yes!!! I should have checked the thread before I left work to see your responses, so I'll have to wait till tomorrow to input everything... but it looks like that's what I want it to do. Thanks ever so much!!!

Replies
13
Views
547
Replies
1
Views
468
Replies
2
Views
565
Replies
1
Views
245
Replies
0
Views
340

1,196,105
Messages
6,013,489
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back