Help required with Excel formula

[WelshWarlock]

Hi all.

This one is probably going to be easy to seasoned veterans but unfortunately I’ve been trying to figure this one out on my own with no luck, although not very gifted when it comes to excel.

I have 3 columns of numbers currently.

Column A represents one persons list of guesses for average attendance numbers per game (football) during the season.

Column B represents another persons set of guesses.

Column C is the actual number.

I’d like a formula for a new Column D, which shows how much closer (or further away) column A was than column B, to the actual number listed in column C.

So if A guesses 10,000 and B guesses 10,500 and the attendance in C was actually 10,400, I would like column D to be “-400”, but should A be closer than B I’d like the number to be listed as a positive.

Hope this makes sense, and thanks very much in advance to anyone who is able to help.

 

[WelshWarlock]

Correction to the above, in the example I gave the answer should be listed as “-300”.

A was -400 off the actual number, B was -100 off the actual number.

Column D should then read -300.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

23 05 25.xlsm
	A	B	C	D
1	A	B	Actual	Diff
2	10,000	10,500	10,400	-300
3	10,500	10,000	10,400	300
4	11,000	12,000	11,000	1,000
5	10,000	11,000	10,500	0
Compare Guesses

Cell Formulas
Range	Formula
D2:D5	D2	=ABS(C2-B2)-ABS(C2-A2)

 

[WelshWarlock]

Incredible sir, thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.