Forum Discussion
use IF or Max or other?
what is the best formula to use when the sum of a range reaches a max number? my range is a running total but I want to know when that total is nearing the max of 30,
If you want to limit the sum to a maximum of 30, you can use a formula like this:
=MIN(SUM(range), 30)
If you want to be alerted when the sum is 30, you can use conditional formatting:
Select the cell (or cells) with the sum formula.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula
=30
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
If you want the cell(s) to be highlighted if the sum is greater than - for example - 28, change =30 to =28 in the above instructions.