Average If problem (again)

Posted by Tanya on December 10, 2001 3:06 PM

I posted this question before, but the response I received didn't work. I'm thinking that maybe I wasn't very clear, so I want to try it again.

As an example, let's say I have a worksheet with the following:
Column A - contains dates
Column B - a number that comes from a vlookup function
Column C - #days derived from networkdays function

I need a formula to solve the following problem:
Average Column C if Column A is null AND Column B ="10"

Will probably need an array formula, but I haven't had much success at creating one that actually works. I would appreciate any help that I could get.

Posted by IML on December 10, 2001 3:11 PM

Not sure what you used before, but try:
=SUMPRODUCT((A1:A10="")*(B1:B10=10)*(C1:C10))/SUMPRODUCT((A1:A10="")*(B1:B10=10))

for a range A1:C10.

good luck

Posted by Aladin Akyurek on December 10, 2001 3:14 PM

Tanya --

How about:

=SUMPRODUCT((LEN(A1:A25)=0)*(B1:B25=10)*(C1:C25))/MAX(1,SUMPRODUCT((LEN(A1:A25)=0)*(B1:B25=10)))

Aladin

===========

Posted by Aladin Akyurek on December 10, 2001 3:46 PM

Just...

gone thru the archives. Found out that I gave you a similar SUMPRODUCT formula and Mark an array formula. What is the trouble that you have with them?

Aladin

===========

Posted by Tanya on December 10, 2001 3:52 PM

Re: Just...

I keep getting a #VALUE! error, and I can't figure out why. It seems as though the formula should work, but it keeps returning this error.

Posted by IML on December 10, 2001 3:56 PM

Re: Just...

Perhaps
=SUMPRODUCT((LEN(A1:A25)=0)*(B1:B25="10")*(C1:C25))/MAX(1,SUMPRODUCT((LEN(A1:A25)=0)*(B1:B25="10")))

or use the existing formula but do a find and replace on your column containing 10 and find
=vlookup and replace all with
=0+vlookup

sorry to jump in, just want to Aladin I like the max in the denomintor to avoid the pesky div by zero errors!

Posted by IML on December 10, 2001 4:01 PM

Nope

that suggestion would fix if you were getting 0, but I don't know why you would be getting the value error.
sorry time to defer to one of the masters...

Posted by Aladin Akyurek on December 10, 2001 4:12 PM

Perhaps...

you could post your VLOOKUP formula?

I assume that you don't have any formulas in A.

============= that suggestion would fix if you were getting 0, but I don't know why you would be getting the value error.

Posted by Tanya on December 10, 2001 4:24 PM

Here's the formula, not exactly VLOOKUP

IF(G2&LT;$AL$3,"",(LOOKUP(G2,$AL$3:$AL$54,$AM$3:$AM$54)))

Basically, the column AL has dates and column AM has a week number associated with that date. This allows me to assign a week number (1-52) to every date that is entered in column G.

I know I'm being a pain, but I really appreciate the help.

Posted by Aladin Akyurek on December 10, 2001 4:47 PM

Modified SUMPRODUCT formula

Basically, the column AL has dates and column AM has a week number associated with that date. This allows me to assign a week number (1-52) to every date that is entered in column G. I know I'm being a pain, but I really appreciate the help.

Tanya --

Your lookup formula returns also "", a value SUMPRODUCT doesn't like. I propose that you try the following modified formula:

=SUMPRODUCT((LEN(A1:A25)=0)*(ISNUMBER(B1:B25))*(B1:B25=10),(C1:C25))/MAX(1,SUMPRODUCT((LEN(A1:A25)=0)*(ISNUMBER(B1:B25))*(B1:B25=10)))

The ISNUMBER test should overcome the problem. If not, please send me a copy of your workbook.

Cheers,

Aladin

Posted by Tanya on December 13, 2001 9:39 AM

Re: Modified SUMPRODUCT formula (Works Great)

Thanks a bunch Aladin, the formula works great!!! : IF(G2&LT;$AL$3,"",(LOOKUP(G2,$AL$3:$AL$54,$AM$3:$AM$54))) : Basically, the column AL has dates and column AM has a week number associated with that date. This allows me to assign a week number (1-52) to every date that is entered in column G. : I know I'm being a pain, but I really appreciate the help. Tanya -- Your lookup formula returns also "", a value SUMPRODUCT doesn't like. I propose that you try the following modified formula: =SUMPRODUCT((LEN(A1:A25)=0)*(ISNUMBER(B1:B25))*(B1:B25=10),(C1:C25))/MAX(1,SUMPRODUCT((LEN(A1:A25)=0)*(ISNUMBER(B1:B25))*(B1:B25=10))) The ISNUMBER test should overcome the problem. If not, please send me a copy of your workbook. Cheers,