Excel Worksheet

d-arke

Can anyone help me?? I'm in work and my boss wants me to create an Excel worksheet with something like a pivot table.

I need something like the following:

Quarter1 Quarter 2 Quarter 3 Quarter 4 Total
Buyer 1 x x x y
Buyer 2 x x y
Buyer 3 x x y
Buyer 4 x y
Total x x x x
If that makes sense! I can do this part.

What I need to know is, how can i use a formula to work out the turnover of buyers in each quarter, for instance how many left after quarter 1, how many came active in quarter 2. or something similar.

if anyone has any ideas and/or are bored in work/home, care to help?????

Tom Dunne

A pivot table might be an overkill for this. You would typically use pivot tables where buyer 1 had 2 or more products per quarter. (Data->Pivot table)

For example:

Buyer Product Q1 Q2 Q3 Q4
1 A 10 5 4 3
1 B 7 10 3 9
2 A 3 14 23 33
2 C 7 2 3 3
and so on...

Subtotals could be an option (From the Data menu->Subtotals)

Example:

Buyer Product Q1 Q2 Q3 Q4
1 A 10 5 4 3
1 B 7 10 3 9
Subtotal: 17 15 7 12
2 A 3 14 23 33
2 C 7 2 3 3
Subtotal: 10 16 26 36
And so on...


Yes I am bored at home. I really don't want to go shopping.

d-arke

tom dunne wrote:

A pivot table might be an overkill for this. You would typically use pivot tables where buyer 1 had 2 or more products per quarter. (Data->Pivot table)

For example:

Buyer Product Q1 Q2 Q3 Q4
1 A 10 5 4 3
1 B 7 10 3 9
2 A 3 14 23 33
2 C 7 2 3 3
and so on...

Subtotals could be an option (From the Data menu->Subtotals)

Example:

Buyer Product Q1 Q2 Q3 Q4
1 A 10 5 4 3
1 B 7 10 3 9
Subtotal: 17 15 7 12
2 A 3 14 23 33
2 C 7 2 3 3
Subtotal: 10 16 26 36
And so on...


Yes I am bored at home. I really don't want to go shopping.

Subtotals could well be an option, i'll have a look and see how they work. I have a list of about 4000 buyers, who we have a total sales figure for in each column. its not broken down into products.....for now!!

what i was thinking was, if Q2 is greater than Q1 it equals 1, then if true, add all the ones and give me a total???????? im only looking for the number of buyers who either retained business with us for the following quarter or left or started business with us compared with the previous quarter

Tom Dunne

d-arke wrote:

what i was thinking was, if Q2 is greater than Q1 it equals 1, then if true, add all the ones and give me a total????????

Then the syntax is

=IF(CELL2>CELL1, 1, 0)

iF CELL2 IS GREATER THAN CELL1 THEN PUT IN A1, ELSE PUT IN 0.

d-arke

ive attached a simple worksheet

any advice