To create a formula that subtracts a certain number from a value and ensures that the result is not greater than another number, you can use the MIN function. Here are the steps:
- Start by typing the formula to reference the cell from another worksheet and subtract the desired number. For example, if you want to subtract 15 from cell A28 in worksheet "2006", the formula would be:
- Next, use the MIN function to ensure that the result is not greater than 5. The MIN function returns the smaller of two values. In this case, the two values are the result of the previous calculation and the value 5. The formula would be:
Formula:
=MIN(2006!A28-15,5)
- Press Enter to calculate the formula and display the result.
This formula will subtract 15 from the value in cell A28 of worksheet "2006" and return the result, but only if it is not greater than 5. If the result is greater than 5, the formula will return 5 instead.