Excel Average Difference.?

Question

I have a list of prices for computer components in an excel spreadsheet, every month i add a new updated price for each component. My frustration is this: i can calculate an Average price for each component easily, by using the average function. However, i want to calculate the average price CHANGE - i.e., the average difference between all the prices. For example

A__B
======
60 65
59 00
58 63
57 61

Column A should give an average difference of 1, whilst column B should give an average difference of 2. Please help!

I know i could do it by using a separate column to calculate the differences, my only problem for that is that theres not just 2 columns, theres like 50, and about 100 rows, which would make it a bit tedious. I want to know if theres an excel formula that will calculate the average difference. The 0 in column B is supposed to represent a gap in the entries - i.e. data thats missing. The solution should be able to deal with this

Answer 1

I don't see how you get a difference of 2 in column B since the change from 65 to 00 is 65 and from 00 to 63 is 63, Even if you mean there's no change from month 1 to month 2, the average comes out to 1.333 because there's a total change of 4 over a period of 3 months. Anyway, here's my solution:
In column C, use this formula to get the differences from column A:
=ABS(A3-A2)
and in column D:
=ABS(B3-B2)
gives you the differences in column B. Start these at the second month, then take their averages.
********* ************ ***********
I'm afraid there is no single formula that does exactly what you want. Maybe if you provide more information about what you're trying to accomplish, I can give you a better solution.

Until then, this formula translates easily to multiple columns and rows. If you have 50 columns, then on the 51s column if you use =ABS(A3-A2) and copy it to the right for 50 columns. You then copy those 50 columns down 100 rows, and you're all set.

Answer 2

Now, do you wnat to ge the diff. between 57 and 61?
or between 58 and 57/
I still need more info here

Send me a sample file with detailed info on my address in my site, and I will try to solve it

After all, I am the VBAXLMan

Answer 3

Try this formula ...

=(MAX(A1:A4)-MIN(A1:A4)+1)/COUNTA(A1:A4)

Your 00 placeholder will need to be changed to TEXT.
A1 is the top of the column, A4 is the bottom of the column.