average(sumproduct...issue

[bluepenink]

Hello

i tried using this to find the average.....but it's not working....not sure at all why

=AVERAGE(SUMPRODUCT(--('Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4),--('Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4),INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0))))

any idea? thx u

 

[moonfish]

Try this instead:

=AVERAGE(--('Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4)*--('Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4)*INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0)))

Confirm with Ctrl+Shift+Enter instead of Enter.


If this doesn't work, could you provide some data examples and an example of a desired result?

 

[bluepenink]

hello

thxs for the prompt response!

when i do a manual check, im getting approx $4,135
the formula you provided gives me $1,824

basically, im trying to sum up data for "chicago" by period (month) and by invoice type (auto, purge).

this is my formula for that:

=SUMPRODUCT(--('Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4),--('Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4),INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0)

so i figured, i can just insert in , average and it will do the same job....any suggestions?

details:
'Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4 > name range "chicago"

'Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4 > name range of invoice "auto"

'Chicago - Data'!$F$2:$K$3640 > columns that contain the invoice amounts $

MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0 > contains month Jan 11, Feb 11 etc

thxs buddy!

 

[bluepenink]

hey buddy

this works

=AVERAGEIF('Chicago - Data'!$A$2:$A$3640,Illinois!$BM$4,INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0)))

but i need to be able to filter by "invoice type"

so is there a way to integrate this into the formula (the line below)?

'Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4 ...> contains invoice type "auto" or "purge"

thx u!

 

[moonfish]

Does this work?

=average(if('Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4,if('Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4,INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0)))))

Confirm with CSE.


Since you already have the sum of the relevant items, if you divide that by the number of relevant items you'll get the average. So it's:

=SUMPRODUCT(--('Chicago - Data'!$A$2:$A$3640=Illinois!$BM$4),--('Chicago - Data'!$E$2:$E$3640=Illinois!$BL$4),INDEX('Chicago - Data'!$F$2:$K$3640,0,MATCH(Illinois!BL$5,'Chicago - Data'!$F$1:$K$1,0)/countifs(--('Chicago - Data'!$A$2:$A$3640),Illinois!$BM$4,--('Chicago - Data'!$E$2:$E$3640),Illinois!$BL$4)

I hope that either of those should get the result you're looking for.

 

[bluepenink]

hello bud

thx u so much for ur awesome help!

truely appreciate it!

cheers