Looks like no one’s replied in a while. To start the conversation again, simply ask a new 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


Thanks

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

Posted on Dec 15, 2017 6:59 AM

Reply
12 replies

Dec 15, 2017 2:44 PM 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.


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 3:01 PM 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 **.

Paul.


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

Dec 17, 2017 4:38 PM 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).


Regards,

Barry

Numbers formulae help please

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.