determine closest entry to given cell

[WLHagen]

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!

 

[Akashwani]

Hi,

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

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check	*	*	Actual	Employee Name(s)	*
2	Bill	98	1	1	*	1	99	Bill	*
3	Cath	95	4	4	*	*	*	Ben	*
4	Ben	100	-1	1	*	*	*	Kath	*
5	Kath	98	1	1	*	*	*	Sue	*
6	Bob	90	9	9	*	*	*	*	*
7	Sue	100	-1	1	*	*	*	*	*
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

 

[Akashwani]

Hi,

On second thoughts........

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check			Actual	Employee Name(s)
2	Bill	21	19	19		37	40	Cath
3	Cath	3	37	37
4	Ben	32	8	8
5	Kath	25	15	15
6	Bob	9	31	31
7	Sue	11	29	29
8
Sheet1

Ak

 

[Akashwani]

Hi,

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

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check			Actual	Employee Name(s)
2	Bill	21	-1	1		1	20	Bill
3	Cath	19	1	1				Cath
4	Ben	32	-12	12
5	Kath	25	-5	5
6	Bob	9	11	11
7	Sue	11	9	9
8
Sheet1

Ak

 

[WLHagen]

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.

 

[Akashwani]

Hi,

Did you mean something like this?....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check			Actual	And the winners are:
2	Bill	21	-1	1		1	20	Bill
3	Cath	19	1	1				Cath
4	Ben	32	-12	12
5	Kath	25	-5	5
6	Bob	9	11	11
7	Sue	11	9	9
8
Sheet1

******

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check			Actual	And the winners are:
2	Bill	98	1	1		1	99	Bill
3	Cath	95	4	4				Ben
4	Ben	100	-1	1				Kath
5	Kath	98	1	1				Sue
6	Bob	90	9	9
7	Sue	100	-1	1
8
Sheet1

*****

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Employee Name	Employee's Guess	Difference	Check			Actual	And the winner is:
2	Bill	21	19	19		8	40	Ben
3	Cath	3	37	37
4	Ben	32	8	8
5	Kath	25	15	15
6	Bob	9	31	31
7	Sue	11	29	29
8
Sheet1

I hope that's what you require.

Ak

 

[WLHagen]

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!!!