Conditional AVERAGE?

[Guest]

I have a Column G2:G300, which contains the difference between dates.

What I need to do is average only rows that have certain values.

To simplify, I K2:G300 only contains two values (True and False)
How do I do an Average of all the columns (G2:G300) where the value in "K"
for that row equals True. I then need to have a seperate average for the
rows that contain False.

Appreciate any help on this.

KSL

 

[JE McGimpsey]

One way (per your simplified example), array-entered (CTRL-SHIFT-ENTER
or CMD-RETURN):

=AVERAGE(IF(K1:K300=TRUE,G1:G300))

 

[Guest]

In a helper cell enter:
=IF(K2,G2,"") and copy down
just average the helper column; AVERAGE() ignores blanks

to average the false entries:
=IF(K2,"",G2)

 

[Peo Sjoblom]

Note that if there is anything else but TRUE or FALSE in K you formulas will
fail
If indeed the only options are TRUE or FALSE then JE's formula can be
changed to

=AVERAGE(IF(K1:K300,G1:G300))


--


Regards,


Peo Sjoblom

 

[Guest]

There are other values; This value contains type of "trouble tickets".
K2:K300 can contain, "New", "Service", "Incident" and a couple others. I
want to Average for "Incident", and and Average for all but "incident"
Eventually, I'll probably want averages for each type.

Thanks.

Kevin

 

[Peo Sjoblom]

Change JE's formula to


=AVERAGE(IF(K1:K300="Incident",G1:G300))


and for all but Incident use

=AVERAGE(IF(K1:K300<>"Incident",G1:G300))

Both entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

 

[Guest]

Perfect. But I forgot to include one piece. I also need to filter out rows
that have a value in X2:X300.

Eventually need to formula:

1. Average all the colums in G2:G300 where the value in K=TRUE and value in
X <>"Cancelled"
2. Average all the colums in G2:G300 where the VALUE in K<>TRUE and value
in X<>"Cancelled"

Thanks again,

KSL.

 

[Peo Sjoblom]

Try

=AVERAGE(IF((K2:K300="Incident")*(X2:X300<>"Cancelled"),G2:G300))



--


Regards,


Peo Sjoblom