Tricky Quotient Formula

[Aimee S.]

Trying to do the following:

IF:
Condition 1: $J$3:$J$5000=1

Condition 2: $C$3:$C$5000="Base"

Condition 3: (TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))

This third condition makes the formula only look at rows where column AF has a date value that falls during the prior month, whatever that may be relative to the current month.

THEN:
Calculate quotient where count of all values in column AV3:AV5000 is the denominator, and count of values in column AV3:AV5000 where "Under" is the value, is the numerator.

While I have Office 2010 I would prefer the answer to be in array format so folks using 2002 Excel can use this tool.

 

[schielrn]

Maybe this, if I understand you correctly?

=SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*($AV$3:$AV$5000="Under"))/SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*($AV$3:$AV$5000<>""))

Hope that helps.

 

[Aimee S.]

Sad panda face Was so hopeful that would work but I just get a #N/A error.

 

[schielrn]

Didn't realize quotient returned the integer value of the division, so try this:

=QUOTIENT(SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*($AV$3:$AV$5000="Under")),SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*($AV$3:$AV$5000<>"")))

Hope that helps.

 

[schielrn]

Sad panda face Was so hopeful that would work but I just get a #N/A error.

Are there #N/A's in your data set, I assume? If so, are they in a specific column or can they be in any of your check columns?

 

[Aimee S.]

Column AV can have either #VALUE! or #N/A

That is the only column that can have those types of returns (this is to be expected in some cases and it does not occur on accident).

So I guess both the numerator and denominator would have to exclude #N/A values, and perhaps #VALUE! as well?

 

[schielrn]

Maybe try this confirmed with control+shift+enter and not just enter:

=QUOTIENT(SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*(IF(NOT(ISERROR($AV$3:$AV$5000)),$AV$3:$AV$5000)="Under")),SUMPRODUCT(($J$3:$J$5000=1)*($C$3:$C$5000="Base")*(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"))*(IF(NOT(ISERROR($AV$3:$AV$5000)),$AV$3:$AV$5000)<>"")))

I didn't test this thoroughly, but it appears to work, I hope you haev what you are expected to get as results?