Help me with an Excel AVERAGEIFS problem

October 20, 2009 1:34 PM

Excel help with AVERAGEIFS: I need to generate an average figure per row, but the cells to be averaged need to meet two criteria.

I have two sheets. To keep it simple, assume one sheet has a start year and a company id number. The other has a list of products made by each company, the year they were released, the company id number, and the revenue for each product.

For each row on the first sheet, I want the average revenue for all products produced by that company id number on or before the start year.

To put it another way, I want to average all revenues in the second sheet where the company id numbers are equal to the company id in the first row, and the release year is less than the start year.

I have tried AVERAGEIFS(revene1:revenueX,companyid1:companyidX,"=sheet1!companyidN",year1:yearX,"
Any hints appreciated

posted by blahblahblah to Computers & Internet (5 answers total)

I think you want to use DAVERAGE for this. You can specify multiple criteria. I had never used the database functions before but was able to set up a dummy example and get the right results out. The fiddly bit seems to be setting up the criteria.
posted by cabingirl at 2:55 PM on October 20, 2009

I don't know how to use AVERAGEIF, so I can't answer for that.

But VLOOKUP might get you close to what you want.

Use VLOOKUP to match the start year to the product on a single sheet, based on the company ID. In fact, if you did that, I bet you could use AVERAGEIF more easily.

If you don't know VLOOKUP (or can't figure it out) memail me an address for an attachment, and I'll send you an example.
posted by Gorgik at 9:12 PM on October 20, 2009

I think you will have the best luck if you use an Array Formula. The help text in Excel doesn't shed a lot of light on how you can use them, but it's a start.

Array Formulas let you count or add up values from one cell based on whether or not other cells in that line meet your criteria. You'd probably need to do this in two steps. First would be to count up the number that meet your criteria, then sum them up and then divide to get an average.

They require some trial and error but they're super useful.

Here's a simple example--hopefully I'm explaining it well.

BALANCE and BASE are two arrays of data (actually, columns in a larger array of data, like what you're working with).

{=SUM((BALANCE<2>
This formula (you have to enter them by pressing CTRL + SHIFT to get the curly braces) looks at each line of the array. If the balance is over 200 and base is 0.35, then you get a 1 (each test returns 1 or 0, for true or false). Each line is evaluated thus. You end up with a string of zeros and ones. Sum just adds up this array. The result is a total: the sum of lines where both are true. You can do OR tests using addition.

I've created these with multiple tests. You'll probably need to do a count first and then a total; then divide to average.

You can add up values thus:

{=SUM((BALANCE)*(BASE=0.35))}

So, for each value of balance, when the corresponding value of base is 0.35, you'll get the balance in your array. If base is <> 0.35, then you get zero. Sum adds up the values.

Hint: don't try to use count instead of sum here because 0 "counts" as a value and you'll just end up with the total number of lines in your array.

Hope this is helpful.

R
posted by robabroad at 9:58 AM on October 21, 2009

You do need an array formula to do this. A formula I have used in the past is :

=AVERAGE(IF((Data!B2:B1200>=DATEVALUE("1/1/07")),--(Data!B2:B1200<>
Which basically translates to - if in row B the date is between January 1, 2007 and December 31, 2007 (your start date) and Row A contains the word Cats (your company number), calculate the average of the corresponding entries in Row C. You'll have to do these for each company you want an average for, separately.

You can manipulate the sheets used, obviously, but you might have to play around with how your Start Date works, as I am not fully understanding how yours functions in comparison with my own.

I believe that the Average has to be separated from the IF to work effectively.

A correction on robabroad above though is that the command is actually SHIFT + ENTER for an array, not CTRL + SHIFT.
posted by urbanlenny at 11:27 AM on October 21, 2009

Sorry, I just realized that my paste was screwed up and how your start date would work.to add after re-reading your question, your start date would just be different for every company, no? You could just use a single Datevalue formula in place of my ones that are "between" two dates to stand for that Start Date for each company, changing it with each company you're doing the average for. Thus:

=AVERAGE(IF((Data!B2:B1200=DATEVALUE("1/1/07")))*(Data!A1:A1200="Cats")),Data!C1:C1200)

And correcting myself - it's actually CTRL + SHIFT + ENTER for an array. D'oh. That's probably what robabroad meant and I misunderstood.
posted by urbanlenny at 11:40 AM on October 21, 2009