# Have sum calculation auto select largest/highest value

Copper Contributor

# Have sum calculation auto select largest/highest value

I have a time tracking spreadsheet that I have the following formula "=B10-(\$B\$4-10)" in place.  The bold section number value of the formula is manually changed to reflect the line of the largest value to give the variance/shortage.  Can this be modified to select this as it occurs?

Please note that the B10 in the formula is from a spreadsheet of 28 persons.  Example below is using same, but it would actually be either B1, B2 etc. depending on the line being calculated.

My spreadsheets have anywhere from 2 to 35 persons where I track hours. I am looking to have the formula auto select the largest value of all listed in column B. In a five-person spreadsheet at week 1, I have AA-24, BB-19, CC-6, DD-11 & EE-17.  I manually change the formula above from "=B10-(\$B\$4-10)" to "=B10-(\$B\$1-10)".  At week 2 it may be AA-29, BB-27, CC-18, DD-30 & EE-25, so the formula would now be "=B10-(\$B\$4-10)".

4 Replies

# Re: Have sum calculation auto select largest/highest value

do you mean something like:
=B10-(MAX(\$B:\$B)-10)

# Re: Have sum calculation auto select largest/highest value

• This gives me the same value for all lines. Maybe in this direction.
• Your assistance is greatly appreciated.

# Re: Have sum calculation auto select largest/highest value

If you enter the formula =B1-MAX(B:B)+10 in a cell, then fill down, the result should change from cell to cell, unless:

• Calculation Options is set to Manual, or
• You have circular references. The formula should not be in a cell in column B itself!

# Re: Have sum calculation auto select largest/highest value

Perfect, thank you so much. This will save me time used for manual formula correction. The formula is actually in column H on my spreadsheet, and I use it for two different reports I generate. Again, thank you HansVogelaar and m_tarler for your assistance.