adding up in a range?

[Jinjunwei]

I have 2 columns of data. I am trying to summarize it by 1000's: Sum all of the revenue for numbers 1-1000, 1001-2000, 2001-3000.. etc. Can anyone think of a good way to do this? I made a formule: =AND(A4<>"",MOD(A4,1000)=0) to show me when a number is divisible by 1000 (where I want my ranges to start/end)... but I am stuck with the sum part. Anyone have an idea? Thank so much in advance. Brian

A B
Number Revenue
1 $1.00
1 $12.00
2 $1.00
2 $21.00
2 $27.00
2 $2.00
3 $2.00
4 $0.00
5 $25.00
5 $26.00
5 $29.00
5 $29.00
5 $25.00

 

[Jinjunwei]

Hi Guys,

Just a quick follow up question. Below is my pivit table with the ranges created. In the right most column I was atempting to divide the sum of the revenue (since the ranges are in 1000s.. i wanted to normalize the revenue to make it per occurance). When I make a simple formula, say in cell b3: b2/1000... I get 20.563. However, I noticed that I can't drag the formula down. When I do, it just copies the formula down as if it contains an absolute reference (even though I removed it). Notice how the 30.563 repeats all the way down. I can override this cell by cell and it works.. but that is sort of combersome. Anyone know how to I can do this last little step?

Row Labels Sum of Revenue
1-1000 30563 30.563
1001-2000 28532.5 30.563
2001-3000 24341 30.563
3001-4000 22060.5 30.563
4001-5000 21995.5 30.563
5001-6000 21854 30.563
6001-7000 18540 30.563
7001-8000 17348.5 30.563
8001-9000 16882 30.563
9001-10000 15329.5 30.563
10001-11000 14900.5 30.563
11001-12000 10770.5 30.563
Grand Total 243117.5

 

[markmzz]

Hi Guys,

Just a quick follow up question. Below is my pivit table with the ranges created. In the right most column I was atempting to divide the sum of the revenue (since the ranges are in 1000s.. i wanted to normalize the revenue to make it per occurance). When I make a simple formula, say in cell b3: b2/1000... I get 20.563. However, I noticed that I can't drag the formula down. When I do, it just copies the formula down as if it contains an absolute reference (even though I removed it). Notice how the 30.563 repeats all the way down. I can override this cell by cell and it works.. but that is sort of combersome. Anyone know how to I can do this last little step?

Row Labels Sum of Revenue
1-1000 30563 30.563
1001-2000 28532.5 30.563
2001-3000 24341 30.563
3001-4000 22060.5 30.563
4001-5000 21995.5 30.563
5001-6000 21854 30.563
6001-7000 18540 30.563
7001-8000 17348.5 30.563
8001-9000 16882 30.563
9001-10000 15329.5 30.563
10001-11000 14900.5 30.563
11001-12000 10770.5 30.563
Grand Total 243117.5

Jinjunwei,

I think that I understood your question.

Try this:

Outside of the pivot table, type =B2/1000 (don't use the mouse).

Copy down now.

Markmzz

 

[Jinjunwei]

Jinjunwei,

I think that I understood your question.

Try this:

Outside of the pivot table, type =B2/1000 (don't use the mouse).

Copy down now.

Markmzz

Ah that did it. Ok.. no more mouse usage within the pivit table. Thanks a bunch!

 

[markmzz]

Ah that did it. Ok.. no more mouse usage within the pivit table. Thanks a bunch!

Jinjunwei,

Thanks for the feedback.

Markmzz