average with 2 criteria

[hockeyb9]

i am trying to set up an average days for inventory based on two criteria.
i have set up a calculation to get the number of days aged already, but
can't get a sumproduct formula to work.

ie) columns
i k L
status financed by days aged
stock x 10
customer x 45
stock y 14
customer z 80
customer x 12

i tried

=(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x"))))

to no avail.
anyone have an idea / solution?
thanks

 

[Mike H]

Try this

=AVERAGE(IF((I1:I200="Stock")*(K1:K200="x"),L1:L200))

Enter as an array by pressing CTRL+Shift+Enter NOT just Enter. If you do it
correctly then Excel will put curly brackets around it {}. You can't type
these yourself.

Mike

 

[PCLIVE]

What's not working? Though you have an unnecessary ( ), it seems to work
with your formula or either of the following:

=SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--($I$1:$I$200="stock"),--($K$1:$K$200="x"))

=(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x"))))

Your sample data only has one match and therefore is divided by 1.

HTH,
Paul

 

[Mike H]

Hmmm,

You got his formula to work as posted? For me it produces a value error as
does your second.

Mike

 

[hockeyb9]

i tried this and got the dreaded #DIV/0!
i did enter as an array.

any idea - in my real data i am matching to an actual cell. ie) instead of
"stock" it's $c$5 so that i can have the same formula for multiple matches.

thanks for your help mike.

 

[Mike H]

Hi,

If you got that then I suggest you check your data. It would happen if it
couldn't find a single mtach in columns I & K or the numbers in column L
aren't really numbers.

The usual culprits are rogue spaces. Manually find a match and check all 3
pieces of data so you are 100% sure you have at least 1 match and DIV/0
should go away but you may still have a problem with other data.

Mike

 

[PCLIVE]

Actually yes. I don't know if something quirky is happening...but all four
formulas, including yours and the OPs, gives the same result. Should it not
work?

--

 

[Mike H]

The OP's didn't for me. The reason is because I used the data layout as
supplied by the OP and with a header that formula will fail


Mike

 

[hockeyb9]

hey guys, i have tried formula both ways.
i hvae rechecked the data to ensure matches - i actually copied the match
cell from data.
still getting the DIV/0!

i really appreciate your help in trying to figure this out because i am
stumped as to why it doesn't work.

 

[PCLIVE]

You may have additional unseen spaces in your data that prevent a match from
being made.

Try this:

=AVERAGE(IF((TRIM(I1:I200)="Stock")*(K1:K200="x"),L1:L200))
committed with Ctrl+Shift+Enter (Mikes formula)

of

=SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x"))

Does that help?

Regards,
Paul


--

 

[PCLIVE]

Good point Mike! I didn't use the headers from the example.



--