analysing sales vs price

[Guest]

I have a large spreadsheet with data in the following format:

Product Price Sales
A $1.00 10
B $2.00 9
C $3.00 8
D $4.00 7
E $5.00 6
F $6.00 5
G $7.00 4
H $8.00 3
I $9.00 2
J $10.00 1

And I am trying to analyse the relationship between price and sales.

I have tried to use a pivot table with the product as the data and then
price and sales as the rows and columns, but then I am getting stuck with
changing the field settings.

Using the above example, Ideally I would like to be able to group the prices
into price breaks of 2.00, and then calculate the total sales for each group.

Is a pivot table the best way to do this and is there any online training
that would cover it?

Or is there other tools within Excel (but not programming or vb) that can
help with calculating the correlation between the price and sales?

THanks

 

[Dav]

If you want to group the values you would have to create a new column
say trunc(price/2). the first way to look for a relationship is to plot
a graph of price vs sales. then visually you can see if there is any
relationship. if there is you can uses regression (analysis toolpack)
or plot a trendline on the graph. Or use various statistical correl,
or linest. But the best guide is to plot the graph first, your eyes are
less likely to deceive you than a statistical model!

Regards

Dav