Having a Spot of Trouble

[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:
Average all corresponding integers that appear in column AH where the integers are greater than or equal to 0.




Also trying the same with a twist:

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"))

THEN:
Calculate the number of times "Under" appears in column AV divided by the combined total number of values "Under" and "Over" appear in that same column, in Percentage format.

 

[Aimee S.]

Also, using Office 2010 But some other users who will open this document have 2002...

 

[barry houdini]

In Excel 2007 or later versions you can use AVERAGEIFS function, i.e. for the first

=AVERAGEIFS($AH$3:$AH$5000,$AH$3:$AH$5000,">=0",$J$3:$J$5000,1,$C$3:$C$5000,"Base",$AF$3:$AF$5000,"<="&EOMONTH(TODAY(),-1),$AF$3:$AF$5000,">"&EOMONTH(TODAY(),-2))

 

[barry houdini]

......In earlier versions you'd need an "array formula", i.e.

=AVERAGE(IF($J$3:$J$5000=1,IF($C$3:$C$5000="Base",IF(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF($AH$3:$AH$5000>=0,$AH$3:$AH$5000)))))

confirmed with CTRL+SHIFT+ENTER

 

[Aimee S.]

It worked perfectly! Thank you very very much.

Still unsure, however, as to how to do the same thing with this logic substituted in place of the Average part:

Calculate the number of times "Under" appears in column AV divided by the combined total number of values "Under" and "Over" which appear in that same column, in Percentage format.

i.e.

Column AV

_Under__
_None__
_Under__
_Over__
_Over__

Here there would be 4 combined instances of either Over or Under.

2 instances of Under divided by 4 combined instances of Over and Under would produce a value of 50%.

That's the other half of my trouble heh

 

[barry houdini]

Try that like this

=AVERAGE(IF($J$3:$J$5000=1,IF($C$3:$C$5000="Base",IF(TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()-DAY(NOW()),"mmmyyyy"),IF($AV$3:$AV$5000="Under",1,IF($AV$3:$AV$5000="Over",0))))))

also confirmed with CTRL+SHIFT+ENTER

 

[Aimee S.]

Outstanding. It works (verified on the backend). How does it work using the AVERAGE formula though? Can you explain what it is doing?

 

[barry houdini]

The formula assigns a 1 to each "under" where the other conditions are met.....and a zero to each "over" so in your example where there are 5 rows where conditions are met the 5 values returned for those rows would be as follows:

1,FALSE,1,0,0

....so when those are averaged FALSE is ignored and the average is the sum of those divided by the count , i.e. 2/4 = 50%...the effect is to give you a percentage of "under"s in the whole population of "under"s and "over"s in the qualifying rows

 

[Aimee S.]

Mindbogglingly awesome. You rawk. Thankies!!!!!!!