Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.


Question: Numbers formulae help please

Hello, a very simple question for some, I'm sure

I would like a formula for Numbers to allow me to reduce a cell value by a different amount month

eg Where A1 = 240

A2 = A1-5

A3 = A1-10

A3 = A1-15

A4 = A1-20

The amount reduced would be constant, in this case 5 but increasing by 5 each month. In this particular example by A48 it would be zero


MacBook Pro (Retina, 15-inch, Mid 2014), iOS 11.2.1

Posted on

Question marked as Solved

Hi Rob,

Is this what you want?

User uploaded file

Formula in A2 (and Fill Down)


Scrolling down,

User uploaded file

Row 49 is zero 😉.

Or maybe I have not understood your question 😕.



Posted on

There’s more to the conversation

Read all replies

Dec 15, 2017 2:44 PM in response to robthegog In response to robthegog

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.)

User uploaded file

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:

  1. 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.
  2. 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.



**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)


Dec 15, 2017 2:44 PM

Reply Helpful

Dec 15, 2017 3:01 PM in response to Barry In response to Barry

Barry, you are right about the **.

Some history about the origin of this formula!!!!

I started the original formula with a "> sign", but changed it afterwards in a "<" sign, but did not thing this modification really through at the time I posted it.

Some time after posting it, I did make the same remark as yours **.


P.S. We still do not know which table rob really wants.

Dec 15, 2017 3:01 PM

Reply Helpful

Dec 15, 2017 5:44 PM in response to stfflspl In response to stfflspl

Hi Paul,

"Some time after posting it, I did make the same remark as yours **"

"Great minds…" 😁

I like the greater flexibility in yours provided by using a cell reference in place of a fixed value.



PS: True. The suspense is overwhelming! 😉

Dec 15, 2017 5:44 PM

Reply Helpful

Dec 17, 2017 4:38 PM in response to Yellowbox In response to Yellowbox

Hi Ian,

Abob's solution depends on the boolean values true and false being interpreted as 1 and 0 respectively. The technique s more useful in MS Excel, where it can be used to turn SUMPRODUCT into a variation of SUMIF.

The parentheses are redundant—as multiplication and division take precedence over addition and subtraction the parentheses aren't necessary to bring t*5 forward.

Numbers handles it OK in this example, but will produce the warning shown.

Otherwise it's the same formula as A1-5 or A1-(1*5) or A1-1*5 (none of which will produce a warning triangle).



Dec 17, 2017 4:38 PM

Reply Helpful
User profile for user: robthegog

Question: Numbers formulae help please