HELP WITH SIMPLE EXCEL FORMULA

Question

Am just starting out with Excel, and am enjoying it immensely.  I've been able to work through a lot of simple formulas, but am stuck on one that seems like it should be really easy.   I'm trying to get an 'average difference' in a group of ascending numbers. Example:   150 220 265                                    Average Difference is ------        56.5 312 376   I also want to be able to add values to the end of the group, and have the 'average difference' update as new entries are added.   Hope someone's willing to help.   Thanks, Art (flgolfnut)

RoHe · Answer

See if this example helps, with 2 different methods for average difference:

http://www.exceltip.com/st/Calculating_the_Average_Difference_between_List_Price_and_Sale_Price_%09/795.html

Hint: click on spreadsheet on this page which is the link to an expanded sheet showing all the details.

Ron

flgolfnut · Answer

RoHe,

I've checked all of Excel's help menu's and a number of websites to see if there was a function that would work for my problem, and I can't find any "cookiecutter" solutions. Your suggestion would work if I were comparing differences between two separate lists. Basically, the formula I'm looking for would be something like this:

(Final Entry minus First Entry) divided by (Total Number of Entries minus 1) = Average Difference, or to use my example in the original question -------

(376-150) / (5-1) = 56.5

I just don't know how to write the formula to make it work!

Art

abach · Answer

Do you have a maximum amount of values in each list?     Message Edited by abach on 08-20-200610:05 AM

mpo · Answer

You can try this formula:   =(MAX(A:A)-MIN(A:A))/(COUNT(A:A)-1)   Assuming the values are in column A, the formula picks out the maximum value from the list, subtracts the minimum value, then divides the result by the number of values minus 1.

flgolfnut · Answer

mpo,

Perfect! This is EXACTLY what I was looking for. I said that I was new to Excel, but I also knew that it would be a fairly simple formula. I worked it out myself, but I had to add an additional column of values to make it work.

Thanks again, and welcome to the Forum. :smileyvery-happy::smileyvery-happy::smileyvery-happy:

Art

Productivity Software

Was this post helpful?