Need some help with Averages

[Noreene Patrick]

I have searched and not found exactly what I need, so here is my question...in fact, I have worked on this for an entire day before I came here for help.

I have 2 efficiencies calculated in my report...the efficiencies are for regular and wave

I need to average the 2 efficiencies to get an average efficiency for the day...some of the efficiencies are 0 and I cant seem to get anything to work.

Here is the last thing I tried:

=avg(nz([regularefficiency]+(nz([waveefficiency]))))

I either get 0 or nothing and sometimes (I dont know how)I get #error.

Thanks, Noreene

PS the regularefficiency and waveefficiency are calculations taking the minutes per order divided by minutes logged in.

 

[MikeAngelastro]

It looks like you need to use a weighted average; this will handle the zeroes. What you do is multiply the efficiencies by 1, add up the results and then divide by the number of instances. You may have to do the multiplication in one query and then feed the result to an aggregate query which adds up the instances and the efficiencies. In a third field you divide the sum of the efficiencies by the sum of the instances for the “group-bys”.

This looks like there is some redundancy here because you are multiplying by 1 in all cases and you would be right. However, this approach will work in all cases because the “instances” are really the weights and usually the weights are different. The nasty thing about averages is that you have to think about what you are after. Often one’s initial approach to calculating averages is off the mark. You have to be very critical of the results you get.

 

[Noreene Patrick]

Thanks Mike for your reply

And I do think you are right..I need to re-think what I want to do...

Noreene