Finding Average with few conditions

Dear Friends,

I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.

Please give me a solution.

Thanks in advance.

acsishere.

In array terms, using say a range of 2:100

Please Login or Register  to view this content.

but the above is expensive, requires error handler, and it looks like you intend to paste across a matrix...

If it were my file...

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

which will be more efficient... a PT may be viable - it's not really clear based on your sample data set.

Dear Sir,

Thanks for your quick reply. It's working nicely. Is it require to use one more column in Col I. Besides, Though it is working nicely when applied, the Lookup is confusing me how it is used.

Is there any possibility to find without creating one more column, Sir?

Can you suggest me?

Thanks in advance.

Dear Sir,

I too agree. If there is no other solution, then I would prefer to choose second option.

Is there any other possibility without inserting another column, for more than 10000 rows. Already my file size is more than 9 MB. I don't want increase it's size and the file is already very slow in it's calculations. That's the reason I am asking for any other solution without inserting a column.

Any possibility?

Thanks in advance.

Dear Sir,

Thank you. That's also working.

Thanks a lot for you both for your efforts.

acsishere.

As Darkyam inferred, a two SUMPRODUCT approach is likely to perform poorer than a single AVERAGE Array.

Arrays and SUMPRODUCT are processed the same way - there is little (if any) performance gain using Sumproduct over an Array - the benefit of the former over the latter is more to do with end-user not being required to use CSE.
It follows then that using 2 SUMPRODUCTs is invariably going to perform poorer than 1 Average Array.

Regardless of the above do not make the mistake of associating quantity of calculations with efficiency.

A 1000 Non Array formulas are invariably going to perform significantly (and I mean significantly) more efficiently than a few hundred Arrays, it all boils down to the way the calcs are processed and the overheads.
Oddly, just today I helped a user elsewhere replace a number of SUMPRODUCTs with SUMIF utilising concatenation helpers and in doing so they found calculation performance improved threefold (in a 50MB file) - ie more formulae can certainly lead to better performance.

So to surmise, I can't emphasise enough just how bad an idea I believe adopting the single cell approach to be in this instance based simply on the volume of calcs being performed.

In all honesty I would not be surprised to see you back complaining of crippling performance issues in the near future