AVERAGE IF statement based on matching condition and date ranges

[simjambra]

Hello folks.

I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.

I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.

It looks something like this:

Please Login or Register  to view this content.

Hoever I would like to create an average on a monthly basis. I was trying to create something along the lines of:

Please Login or Register  to view this content.

But to be honest, I think I am way off.

Any ideas folks? Thanks

Simon

[DonkeyOte]

One way...

Please Login or Register  to view this content.

The above will account for #DIV/0! errors also - and can be applied across all months / rows.

[simjambra]

Hi Donkey,

Thanks for having a look at the table. I have given this a go and I am just getting 0% in each cell, could you just check I am doing it right?

Many thanks

Simon

[DonkeyOte]

Simon, check the date in A4 on source sheet.

[simjambra]

That is perfect, thank you so much.

[DonkeyOte]

No problem... you know you can use a PT to generate the table
(though obviously it will only show values for those months with values)

See attached:

[simjambra]

Blinking flip, to be honest I don't know why I didn't think of that in the first place!!

Yes that would probably be the most straight-forward route and I will probably go with the pivot table... but it's good to have the other one because I will now try and get my nut around how you created that formula. I am always trying to learn and one day I might be able to provide my own solutions to this fantastic forum. Some way off yet, mind you... haha.

Thanks again donks. You are a star.

[DonkeyOte]

... I will now try and get my nut around how you created that formula.

Best to break out formulae into component parts

Please Login or Register  to view this content.

First the AVERAGE array...

Please Login or Register  to view this content.

This will generate an array of values such that...

Where trainer value = A3 and Date = B$2 you will get the corresponding value from tots range

Where either of the above does not hold true FALSE is returned

End result ... you have an array of values to Average such as:

Please Login or Register  to view this content.

The Average will ignore the Boolean False (it's looking for numbers) so you effectively end up with an Average of 0.65 (0.5 + 0.8 / 2)

However, if you should end up with only FALSE values in your Array, eg:

Please Login or Register  to view this content.

The result is obviously #DIV/0!
(given you're dividing by 0 (no numbers))

The most common approach to ignore errors is to double evaluate... however you can (if you know the data type being returned) use an alternative approach...

Please Login or Register  to view this content.

Because we know we're returning a number we can use LOOKUP to return the biggest number from an array of 2 values... the 2 values are generated by:

Please Login or Register  to view this content.

So we end up with 2 values, 0 and the result of our AVERAGE array

The LOOKUP with BIGNUM (9.999etc....) will ensure that the last of the numbers listed is returned
(this utilises the binary search algorithm but I won't try and explain that (as I can't... very well))

So using our 2 examples where AVERAGE returns 0.65 and #DIV/0! respectively...

Please Login or Register  to view this content.

Whereas

Please Login or Register  to view this content.

Values in the array that do not match the criteria value (bignum) are ignored... so in the above the Error value is ignored and the 0 returned... thereby avoiding need to double evaluate... if the Average returns a numerical result the LOOKUP will return it, if it generates an error 0 is returned.

I hope that helps (some)

(Of course if you're running 2007 you can use IFERROR(AVERAGE(...),0) ... and even look into using AVERAGEIFS ... note neither are backwards compatible)

[simjambra]

I am using 2007... what was that last formula?

You are spoiling me, I really do appreciate it!

[DonkeyOte]

Using your old layout and using 2007 formulae:

Please Login or Register  to view this content.

Note: the above does not require CTRL + SHIFT + ENTER

For more info on the above (IFERROR & AVERAGEIFS) check out XL Help.

[simjambra]

I have only just bought Excel 07 so I wasn't aware of these built in array formulas but that's really handy, cheers.