Formula to Auto Subtract Cells That Change Weekly

[elkerpride]

Rep (Column A)	Sales (Column B)	$$ Behind Leader (Column C)
Mike	$ 21,527,850.00	$ 2,651,193.00
Moe	$ 9,672,507.00	$ 14,506,536.00
Tony	$ 24,179,043.00
Greg	$ 23,814,970.00	$ 364,073.00

Hello,

Is it possible to use a formula to determine which cell in the Sales Column (Column B) is the highest number and automatically subtract the remaining 3 cells in column B from it, using the respective cell in column C to show the difference?

In the above example, Since B4 is the highest number currently, I would need to take B4 and subtract B2 from it and the result would go in C2 and likewise with subtracting B3 from B4 and placing it in C3 along with B5 from B4.

I get that it is basic subtraction. The issue is the totals (Column B) change weekly and any one of the reps could have the highest number on any given week. There are significantly more reps than the 4 shown so I was hoping to be able to automate this process if possible.

Sorry, I don't know excel lingo that well. Please let me know if any clarification is needed & thanks in advance.

 

[6StringJazzer]

Here you go:

$scratch.xlsm
	A	B	C
1	Rep (Column A)	Sales (Column B)	$$ Behind Leader (Column C)
2	Mike	$21,527,850.00	$2,651,193.00
3	Moe	$9,672,507.00	$14,506,536.00
4	Tony	$24,179,043.00
5	Greg	$23,814,970.00	$364,073.00
Sheet8

Cell Formulas
Range	Formula
C2:C5	C2	=LET(Diff,MAX($B$2:$B$5)-B2,IF(Diff=0,"",Diff))

 

[elkerpride]

Perfect, thank you!

 

[elkerpride]

Hello,

I didn't realize that the people I am sending this update to are using Microsoft 2016 and are getting the #NAME? error when they open it. Assuming this function was not around for 2016 version.

Is there by chance another way to do this where it is compatible with 2016 edition?

If not, I'll make it work...thank you.

 

[6StringJazzer]

This is the equivalent without using LET

=IF(MAX($B$2:$B$5)-B2=0,"",MAX($B$2:$B$5)-B2)