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?????

kippy

http://www.boards.ie/vbulletin/showthread.php?t=2054866258

I think you've triple posted this in three seperate forums-the one above it the one I found straight away......
Why did you do this?

d-arke

kippy wrote:

http://www.boards.ie/vbulletin/showthread.php?t=2054866258

I think you've triple posted this in three seperate forums-the one above it the one I found straight away......
Why did you do this?

basically wasnt sure where would be the best place to post it.

kind of falls into all three so hope someone can come up with an answer

seamus

It should be simple.

Post some sample data. Where you have x's above - what does this represent?

What exactly are you trying to caculate?

d-arke

The X represents a total sales figure (100-99,0000) or thereabouts from a specific person. So for instance, buyer1 spent say 100k with us in Q1, 50K IN Q3, But nothing in Q2 or Q4.

Not exactly sure to be honest what im trying to calculate. Was told to have a think about it and see what i could come up with. Basically, if i can total up everybody who purchased in Q1-Q4, and find out the value of total buyers in each quarter, how many of those didnt buy in the following month, and which buyers didnt buy in Q1 bought in Q2.

Make any sense?

seamus

The totals are easy. I'm going to assume that you know how to do that.

To find out, for example, which people did buy in Q1 and not in Q2, I'd stick in a column between Q1 and Q2 - let's say it's column D. Each cell in this column uses an IF. IF Q1 <> 0 AND Q2 = 0 THEN the value of the cell is 1 ELSE the value of the cell is zero.

Then apply a SUMIF function to another cell, and sum up all the values in column D where the value of the cell = 1. This will give you a count of the number of buyers who bought in Q1 and didn't buy in Q2. Hide Column D to keep your sheet tidy. You can do this for each of the other pairs then.

d-arke

seamus wrote:

The totals are easy. I'm going to assume that you know how to do that.

To find out, for example, which people did buy in Q1 and not in Q2, I'd stick in a column between Q1 and Q2 - let's say it's column D. Each cell in this column uses an IF. IF Q1 <> 0 AND Q2 = 0 THEN the value of the cell is 1 ELSE the value of the cell is zero.

Then apply a SUMIF function to another cell, and sum up all the values in column D where the value of the cell = 1. This will give you a count of the number of buyers who bought in Q1 and didn't buy in Q2. Hide Column D to keep your sheet tidy. You can do this for each of the other pairs then.

Okay, Excellent idea. I've just tried it. Its along the lines of what Im looking for. I'll get back to you

d-arke

okay i've uploaded a simple worksheet to get my head around this.

any advice:

gained: i think i have this more or less correct

lost: any advice

retained: any advice

sorry, im hopeless. dunno how i got the job. though in fairness, its not really what im suppose to be doing. trying to earn some brownie points seeing as its quiet

seamus

I assume a person "Lost" in Q2, for example, is someone who had sales in Q1 but didn't have sales in Q2?

If so, stick the following formula into J3, and see if you can understand what's going on:
=IF(C3 <> 0, IF(G3=0, 1, 0), 0)