average instead of total

[norm01]

I need the formula to return an average istead of a total: =SUMPRODUCT(--(TRANSPORT!$A$9:$A$89>=SUMMARY!$C$4),--(TRANSPORT!$A$9:$A$89<=SUMMARY!$D$4),--(TRANSPORT!$E$9:$E$89))


Thank you in advance

[MarvinP]

Hi Norm,

How about a newer version of Excel where they have this function built in:
http://www.exceltip.com/st/AVERAGEIF...2007/1374.html
They were listening to old 2003 customers when they put this in 6 years ago.

[norm01]

Hi Norm,

How about a newer version of Excel where they have this function built in:
http://www.exceltip.com/st/AVERAGEIF...2007/1374.html
They were listening to old 2003 customers when they put this in 6 years ago.

Unfortuanyly this is a company program so I am stuck with 2003 I also forgot to mention this data I need the average for are percents

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[MarvinP]

Have you heard of CSE (Control Shift Enter or Array) formulas?
You build an array of 1 or 0 for true or false based on your first test. Then you do it with an array on the second test. You multiply these 1s and 0s together and only cells that pass both tests get throught the filter. Then you average the values that get through this filter. DonkeyOte is the guru on these.

Try this

Please Login or Register  to view this content.

I may have an extra parin. Now you don't press enter with the formula. You need to hold down the Ctrl and Shift key and then press Enter. Curley braces will surround the formula { } so you know it is a CSE formula.

Hope this helps.

[DonkeyOte]

Generally a good idea to avoid coercion of final range wherever possible - just in case there are non-numerics to be ignored
(direct coercion of non-numerics would otherwise result in #VALUE! error)

Please Login or Register  to view this content.

the other advantage of adding the IFs is that as soon as a test returns FALSE the additional tests are ignored thereby reducing (slightly) the number of calculations being performed within the Array itself.

If you wanted to avoid Array entry then another alternative would be:

Please Login or Register  to view this content.

a little longer but pretty efficient nonetheless

You could of course also use SUMPRODUCT/SUMPRODUCT but IMO a single Array is preferable to two SUMPRODUCTs from a performance perspective.

[squiggler47]

=SUMPRODUCT((TRANSPORT!$A$9:$A$89>=SUMMARY!$C$4)*(TRANSPORT!$A$9:$A$89<=SUMMARY!$D$4),--(TRANSPORT!$E$9:$E$89),1/sum((TRANSPORT!$A$9:$A$89>=SUMMARY!$C$4)*(TRANSPORT!$A$9:$A$89<=SUMMARY!$D$4)))

[DonkeyOte]

@squiggler47 - that won't work with the coercion as you have it - see below

=SUMPRODUCT((TRANSPORT!$A$9:$A$89>=SUMMARY!$C$4)*(TRANSPORT!$A$9:$A$89<=SUMMARY!$D$4),TRANSPORT!$E$9:$E$89*(1/SUM((TRANSPORT!$A$9:$A$89>=SUMMARY!$C$4)*(TRANSPORT!$A$9:$A$89<=SUMMARY!$D$4))))

I would still of course argue that the above is (even) more inefficient than the equivalent Array (per posts 5 & 6)

[squiggler47]

@DonkeyOte
Yes, I should have tested it!

and yes the above is less efficient, but I though it relevant as a modification of the original, I tested all 3 formulas and your second none array formula is twice as fast as the array version!

[DonkeyOte]

Agreed, SUMIF & COUNTIF will perform much better with large datasets... Arrays & SUMPRODUCT should IMO always be method of last resort esp. when used in either:

a) large quantity
b) in conjunction with large precedent ranges
c) in Volatile context
or
d) any combination of the above !