# Problem using Evaluate to calculate Cumulative Sales

1. ## Problem using Evaluate to calculate Cumulative Sales

Note:
I already have a routine that will use a loop to determine Cumulative Sales.

My goal here is to use an Evaluate in cell formulas to obtain the
same results:

There are 12 ROWS related to Columns A B C

Column A is TIME with values 1 to 12

Column B is Sales with these Values

{10;25;100;250;600;1200;2000;3800;4900;2800;2100;800}

Column C is to be Cumulative Sales
which I cannot get to work

Below is code that fills up Columns A and B and FAILED code for Column C

The ONLY SOLUTION I am after is an Evaluate solution and this ASSUMES
that Evaluate can do this. I could be WRONG.

``Please Login or Register  to view this content.``
Thanks in advance if anyone goes after this.

John

2. ## Re: Problem using Evaluate to calculate Cumulative Sales

Hi

This is a follow-up to the "Evaluate" post above.

The solution below (I think) maybe much better than doing a loop.

If anyone has a Sales Column and needs a Cumulative Sales column
to the right of it - AND DOES NOT WANT TO DO A LOOP - the following
will get you there:

Rng_C_Sales_CUMU.FormulaR1C1 = "=IF(ISNUMBER(R[-1]C[-1]),RC[-1] + R[-1]C, RC[-1])"

Metaphorically the above is saying IF( A , B , C) where

ISNUMBER(R[-1]C[-1])
A = if 1 row UP and 1 column to the LEFT is a NUMBER then

Note:I originally used IsNumeric and some kind soul bailed me out of that
and suggested ISNUMBER.

RC[-1] + R[-1]C
B = this row and column to LEFT + UP 1 row THIS column

C = THIS row 1 column to LEFT
RC[-1]

The next two lines do the following:

LAGS the Cumulative Sales column presented above and which is to its left
Rng_C_Sales_CUMU_Lag.FormulaR1C1 = "=IF(ISNUMBER(R[-1]C) , R[-1]C[-1] , 0)"

SQUARES the Lagged Cumulative Sales column presented above and which is to its left
Rng_C_Sales_CUMU_Lag_SQR.FormulaR1C1 = "=IF(ISNUMBER(RC[-1]) , RC[-1]^2 , 0)"

Hopes this helps someone get there cleaner and faster.

regards
John

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1