HI Rob,
Here's a pair of examples.
The first (column A) uses the same formula as Ian has used above.
The second (column B) uses a formula that more closely follows your example. Here, the increasing value to be subtracted from the initial 240 is calculated by the part of the formula following the - sign. ( 5*(ROW()-1) )
The formula shown below the table is from cell B2. The formula is filled down to the rest of the cells in column B.
(Rows 7-42 are hidden to make the screen shot fit here.)
The $ operator before the 1 in B$1 locks that reference to row 1 as the formula is filled down the column.
Both my formula and the version offered by Ian put the base amount to be subtracted ( 5 ) into the formula itself.
Paul's formula offers two enhancements:
- In the core formula, (shown in his post as it would appear in cell C23) Paul uses a cell reference ( C$2) to get the base amount to be subtracted. This allows the base subtraction amount to be changed in Row 2 of the table without need to edit the formula.
- My formula, and Ian's, if filled beyond row 49, will produce negative values. Paul's adds a section which prevents negative values by replacing them with 0 when the previous result is less than the amount to be subtracted OR when the previous result is less than 0.**
Paul adds one more enhancement: a custom format, applied to all non-header cells in the table to 'not display' a number that is zero or negative. This provides a visually 'cleaner' table if yours resembles his multi-column solution, but may be unnecessary if you make the table only as big as needed to show the positive results.
Regards,
Barry
**The OR in Paul's formula is redundant. Any result in C22 that is less than or equal to 0 is automatically less than the amount in C$2, so only the first test is needed.
IF(C22<C$2,0,C22-C$2) will give the same result as IF(OR(C22<C$2,C22â€0),0,C22-C$2)
B.