Forum Discussion
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)".
- do you mean something like:
=B10-(MAX($B:$B)-10)- This gives me the same value for all lines. Maybe in this direction.
- Your assistance is greatly appreciated.
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!
