Creating a running list of sales numbers

[dlem7]

Hi folks,

I'm currently working on an excel sheet where I am attempting to summarize sales numbers.

In this scenario I have what I call "Account Behavior". Basically when an account is registered, I expect some sort of shopping behavior at a store. In month 1, they will spend $100. In month 2, they will spend $50. In month 3 they will spend $30 (so on and so forth).

Next I have another field I call "New account growth". This is a field that says our store expects 10 new accounts in month 1, 12 in month 2, 15 in month 3, etc.

What I need help with is summarizing this data. I want to create a third field called "Total Sales by month". In the example outlined above, my first month would simply be my first ten accounts making a purchase of $100 each (10*100=$1000). The second month would then have 10 accounts making a purchase of $50, and 12 new accounts making a purchase $100. My formula would then be 10*50+12*100 = $1700.

My biggest issue is that I'm working with 10 years of projections split out by month so writing out the formulas is extremely tedious. I came here hoping to find a faster way/method for summarizing this data.

Below I listed a short example. I hope the formatting comes through

Month on Book	1	2	3	4	5	6
New Account Sales	$100	$50	$20	$18	$16	$14
Calendar Month	Jan	Feb	Mar	Apr	May	Jun
Account Openings	10	12	5	8	11	7
Total Sales	10*$100	10*$50+12*$100	10*$20+12*$50+5*$100	?	?	?

<tbody>
</tbody>

 

[HackSlash]

It looks like you can write your equation with relative references to the cells above. Then copy this across the whole row.

Click in the first Total Sales Cell and type =
Then click the account openings cell above it
Then *
Then click the cell for New Account Sales

You should end up with something like this:

=B4*B2

In column 2 make a similar equation that also adds the total before it.

This should look like:

=(C4*C2)+B5

Now you can repeat the equation in the second cell across the whole row.
With your second cell selected: grab the little box in the lower right hand corner of the cell and drag it to the right. This will make relative references in all the cells you drag it to.

Magic.

Let me know if that is what you were looking for.

 

[dlem7]

It looks like you can write your equation with relative references to the cells above. Then copy this across the whole row.

Click in the first Total Sales Cell and type =
Then click the account openings cell above it
Then *
Then click the cell for New Account Sales

You should end up with something like this:

=B4*B2

In column 2 make a similar equation that also adds the total before it.

This should look like:

=(C4*C2)+B5

Now you can repeat the equation in the second cell across the whole row.
With your second cell selected: grab the little box in the lower right hand corner of the cell and drag it to the right. This will make relative references in all the cells you drag it to.

Magic.

Let me know if that is what you were looking for.

Unfortunately this doesn't get to what I need but thank you for taking the time to reply.

In the example you listed above the 12 new accounts in month 2 are making a purchase of $50 and the first 10 accounts arent making a purchase of $100 again.

My excel formula for the total sales in month 1 looks like "B2*B4". Month 2 should look something like "C2*B4+B2*C4". Month 3 would look like "D2*B4+C2*C4+B2*D4"