Can you do a average with criteria from another column?

**dredd2525** · 10-10-2006, 04:13 PM

I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)

**georgette** · 10-10-2006, 04:42 PM

you could always try what i do (i'm no genius at this stuff, but it always works):
do a sumif divided by a countif. in other words, use the definition of averaging.

example: sumif(column b stuff, criteria, column c stuff)/countif(column b stuff, criteria)

mine specifically looks like: sumif($b$2:$b$20,"G",c2:c20)/countif($b$2:$b$20,"G")

it sums the scores (in column c) if the student is a girl (determined by a "G" or "B" in column b) and then divides by the number of girls it counts. i have another one for boys. it works.

**kraljb** · 10-10-2006, 05:16 PM

Try this...

=SUMPRODUCT(--(TEXT($C:$C,"mmmm")="July"),$B:$B)/SUMPRODUCT(--(TEXT($C:$C,"mmmm")="July"))

Hope it helps...
John

**dredd2525** · 10-10-2006, 05:24 PM

thank you Kraljb. Ok one last thing though. I'm trying to make this report auto update. so each entry has to take this month - 12 for the first average, then this month -11 for the next. I've been fighting this part for a while and finally figured it out in Access, but of course that command isn't available in Excel. I need it to figure specifically on month so that the report can be run at any time within a month and still get the same results.

Also my date format is 07/21/2006 10:49

When I enter in your function and make the appropriate chagnes i get a num! error. here is a copy of the function:
=SUMPRODUCT(--(TEXT($G:$G,"mmmm")="july"),$K:$K)/SUMPRODUCT(--(TEXT($G:$G,"mmmm")="july"))
G is the resolve date column
K is the time to resolve column

**kraljb** · 10-10-2006, 05:50 PM

That is odd that it is returning the num error. I tried it on mine, with making both parts text, and each individually to see if that would duplicate the problem, I cannot get it to duplicate...

However, to clarify your first question...

You want to look at the averages for a year ago, and 11 months ago? Then it requires a little more work because you also have to take into affect the year and not just the month....

**dredd2525** · 10-10-2006, 05:54 PM

Originally Posted by kraljb

That is odd that it is returning the num error. I tried it on mine, with making both parts text, and each individually to see if that would duplicate the problem, I cannot get it to duplicate...

However, to clarify your first question...

You want to look at the averages for a year ago, and 11 months ago? Then it requires a little more work because you also have to take into affect the year and not just the month....

yeah, the averages from up to a year ago. the table will always only have up to 12 months of data. so up to october of last year.

**dredd2525** · 10-10-2006, 05:48 PM

woot, granted some momentary inteligence. thank you to gorgette for the formula I got to work.

=SUMIF($I:$I,MONTH(NOW())-3,K:K)/COUNTIF($I:$I,MONTH(NOW())-3)

**dredd2525** · 10-10-2006, 05:53 PM

ok, completely cosmetic. but this still comes back to my 2nd problem. How do I get the label for the data to change?
=MONTH(NOW()) gives me the number of the month. but not the name. is there a better way?

When I tried to change the format it just says January.

**kraljb** · 10-10-2006, 05:55 PM

=TEXT(MONTH(NOW()),"mmmm") will return the string "October"

Glad to see you got things working...

**dredd2525** · 10-10-2006, 06:06 PM

maybe it is just me.
I pasted and manually typed that string and got "January"
My computer cant tell time. that is re-assuring.

**dredd2525** · 10-10-2006, 06:13 PM

ok, figured out a really "ghetto" way of doing it. but if anyone has a better way I'm all ears.
=IF(MONTH(NOW())-3=7,"July",IF(MONTH(NOW())-3=8, "August")) so on and so forth for all 12 months.

**martijn.de.gruiter** · 04-24-2013, 04:36 AM

Originally Posted by dredd2525

woot, granted some momentary inteligence. thank you to gorgette for the formula I got to work.

=SUMIF($I:$I,MONTH(NOW())-3,K:K)/COUNTIF($I:$I,MONTH(NOW())-3)

perfect and simple solution, thank you!!