SumProduct - Average

[Stevedarby02]

Does anyone out there know the quivalent formula to return average rather than sum as it does in sumproduct.

I'm after an array formula without using the 'control, shift and enter' route. This is because these type of formula's take a vast amount of CPU resources when you start building them up and limited to the number of arrays (whereas there are 30 available in sumproduct)

Sumproduct reduces the resource by 20% which is great, but i'd want an Avgproduct (although i know that doesn't technical exsist).

I also don't want to go down the route of using a /countif formula to get an average as this will ensure the formual becomes very large as i've got a number of columns in my array to start with.

Anyone got any ideas? (not including VB).

Thanks

Steve

[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!

[Stevedarby02]

I've attached a test sheet showing what sumproduct is doing which works fine when i need a sum, but want the same sort of formula to return the average.

Thanks

Steve

[zbor]

How about Pivot table?

Also, you didn't write a excel version since in XL 2007 and on you could use =AVERAGEIFS function

[Stevedarby02]

How about Pivot table?

Also, you didn't write a excel version since in XL 2007 and on you could use =AVERAGEIFS function

Thanks, but that wouldn't suit the format that i need. I can't attach the sheet i'm currently using arrays as there is a mass of data on it.

Thanks
Steve

[Stevedarby02]

Sorry i forgot to say i'm using 2003 and so is the business i'm working at. I've seen the new formula's in 2007, but can't use them.

Thanks

[zbor]

Only AVERAGEIFS function meet you request.

Anything else is either SUMPRODUCT either ARRAY function.

To avoid it you can use Pivot table or Filter as in example.

Filter your values and you'll get required numbers.

[Stevedarby02]

Only AVERAGEIFS function meet you request.

Anything else is either SUMPRODUCT either ARRAY function.

To avoid it you can use Pivot table or Filter as in example.

Filter your values and you'll get required numbers.

That's a shame, filter/pivot wouldn't work as the attached sheet is just a dummy and nothing like the final version which has multiple tabs and formulas across it rather than looking at the data tab.

Thanks

Steve

[zbor]

Then try to reduce number of volatile formulas and keep ranges to minimum...
(use for example A1:A1000 instead of A:A)

[DonkeyOte]

Use concatenation to create a unique key - you can then avoid SUMPRODUCT/Array by virtue of a SUMIF/COUNTIF (utilising wildcards as nec.)

Using your sample file to illustrate concept:

Data!E2:
=":"&B2&"::"&C2&":"
copied down

Sheet1!B5:
=SUMIF(Data!$E:$E,":HR::1:",Data!$D:$D)

Sheet1!B9
=B5/COUNTIF(Data!$E:$E,":HR::1:")

A greater number of lightweight calcs (as above) will perform far better than Array/SUMPRODUCT equivalents - esp. when used in conjunction with large datasets.

[Stevedarby02]

Use concatenation to create a unique key - you can then avoid SUMPRODUCT/Array by virtue of a SUMIF/COUNTIF (utilising wildcards as nec.)

Using your sample file to illustrate concept:

Data!E2:
=":"&B2&"::"&C2&":"
copied down

Sheet1!B5:
=SUMIF(Data!$E:$E,":HR::1:",Data!$D:$D)

Sheet1!B9
=B5/COUNTIF(Data!$E:$E,":HR::1:")

A greater number of lightweight calcs (as above) will perform far better than Array/SUMPRODUCT equivalents - esp. when used in conjunction with large datasets.

That works up to the point where you are using lookup values to change the result. For example i'd have a lookup with all department names in and when one is selected it's used in the formula and returns X result. Concatenation would result in my having to use that value in the lookup rather than the actual name of the department.

There is some fiddly bits of work i could do to get it to point where i want, but probably worth just looking at office 2007.

Thanks

[DonkeyOte]

No need to quote entire posts in your reply this simply clutters up the thread.

I'm afraid I don't really follow what you're saying in the above ... or rather I don't understand the significance.

If the department is a variable then modify the criteria in the SUMIF/COUNTIF accordingly, eg:

=SUMIF(Data!$E:$E,":"&$A$1&"::"&$B$1,Data!$D:$D)
where A1 / B1 hold the "variables"

the key is not affected in any way, shape or form.

edit:
On an aside, if you find yourself using a model in which calculating time is oppressive then that's normally a fairly good indication that the model needs to be redesigned.