Finding a Monthly Average with Two Conditions

  • Thread starter Thread starter needs more thrutch
  • Start date Start date
N

needs more thrutch

Greetings,
I am a green bean newbie to writing Excel formulas and desperately
need some help. I am working on a Staffing formula which will
generate monthly averages. Below are the columns that I need to work
with ...

(Column A) (Column B) (Column C) (Column D)
CODE POSTED START DATE DAYS TO FILL
NT 2/14/03 5/5/03 81
NT 3//11/03 3/24/03 13
HR 4/11/03 7/28/03 107
IS 6/8/03 7/7/03 29
IS 7/14/03 8/4/03 20
IS 12/20/02 1/15/03 25
IS 11/18/02 1/6/03 48

For example, I need to find the average days to fill it took to fill
all IS jobs which had a start date in January.

I guess what I am trying to do is have the formula look at the Codes
in Column A, first. If "IS" is noted in the column, the formula then
needs to look in Column C to find all jobs which have a start date
falling in January. If these conditions are met, I need an average
days to fill for all IS jobs with a start date in January.

Please let me know if I'm not clear in my example. Again, I'm very
new to working with formulas and doing searches within this newsgroup
has been the foundation of my success as of yet.

Many thanks in advance,
Steve
 
For the month 1 without checking the year involved...

=AVERAGE(IF((A2:A8="IS")*(MONTH(C2:C8)=1),D2:D8))

For the month 1 in 2003...

=AVERAGE(IF((A2:A8="IS")*(TEXT(C2:C8,"m/yy")="1/03"),D2:D8))

You need to confirm these formulas by hitting control+shift+enter, not by
just enter.
 
A B C D E F G
1 CODE POSTED START_DATE DAYS_TO_FILL CODE
2 NT 2/14/03 5/5/03 81 IS FALSE
3 NT 3/11/03 3/24/03 13
4 HR 4/11/03 7/28/03 107 36.5
5 IS 6/8/03 7/7/03 29
6 IS 7/14/03 8/4/03 20
7 IS 12/20/02 1/15/03 25
8 IS 11/18/02 1/6/03 48
9

G2: =AND(C2-"2003/1/1">=0,C2-"2003/1/31"<=0)
F4: =DAVERAGE(A1:D100,4,F1:G2)
 
Back
Top