CODEWITHSUNDEEP
excelmaxaverage
user1308447
user1308447

Reputation: 23

How do I calculate average in Excel between an undefined set of cells?

I have a table that looks like this:

User         Interval  
270          new user  
270          30  
270          18  
270          7  
295          new user  
295          50  
295          30  
310          new user  
310          40  
317          new user

For each user, I'd like to know the average and the max interval. My problem is that I don't know how to calculate an average that will run only between 2 values of new user, and the amount of intervals per user is not set. Is there a way to do it in Excel?

Thanks!

Upvotes: 0

Views: 961

Answers (2)

barry houdini
barry houdini

Reputation: 46371

As codenut says you can use AVERAGEIF for an average (If you have Excel 2007 or a later version) but there is no "MAXIF" function so you need an "array formula" for that.

Assuming data in columns A and B try this formula in C2

=IF(B2="New user",AVERAGE(IF(A2:A$1000=A2,B2:B$1000)),"")

once you have pasted that in C2 you need to press F2 to select formula then hold down CTRL and SHIFT keys and press ENTER. That will put curly braces like { and } around the formula in the formula bar - now copy formula down column.

Repeat for D2 with exactly the same formula except replace AVERAGE with MAX

You will get the max and average for each user at the top of each with other lines blank

This works for up to 1000 rows - change formula as required if you have more data.

Upvotes: 2

codenut
codenut

Reputation: 56

perhaps the averageif function can help you out? http://office.microsoft.com/en-us/excel-help/averageif-function-HA010047433.aspx

Upvotes: 0

Related Questions