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

Question:

# Question:Q: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

Thanks

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

Posted on

Question marked as Solved

Hi Rob,

Is this what you want?

Formula in A2 (and Fill Down)

=A1−5

Scrolling down,

Row 49 is zero 😉.

Or maybe I have not understood your question 😕.

Regards,

Ian.

Posted on

There’s more to the conversation

Question marked as Solved

Hi Rob,

Is this what you want?

Formula in A2 (and Fill Down)

=A1−5

Scrolling down,

Row 49 is zero 😉.

Or maybe I have not understood your question 😕.

Regards,

Ian.

Dec 16, 2017 5:13 AM

Another way would be to use the ROW() function.

Here rows 11:45 are hidden to save space in posting.

SG

Dec 15, 2017 2:05 PM

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:

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.

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.

Dec 15, 2017 2:44 PM

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

Paul.

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

Dec 15, 2017 3:01 PM

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.

Regards,

Barry

PS: True. The suspense is overwhelming! 😉

Dec 15, 2017 5:44 PM

Hi Abob,

B=a1-(t*5)

In spite of the blue warning triangles, It works! Filled right (as I assume you intended) or filled down.

For the benefit of me and other users, please explain. You have me puzzled 😕.

Regards,

Ian.

Dec 17, 2017 1:54 AM

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

Regards,

Barry

Dec 17, 2017 4:38 PM

User profile for user: robthegog

Question: Numbers formulae help please