Conditional AVERAGE?
 

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

Conditional AVERAGE?
 

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

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


In article ,
Leonhardtk wrote:

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

Conditional AVERAGE?
 

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)
--
Gary''s Student - gsnu200745


"Leonhardtk" wrote:

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

Conditional AVERAGE?
 

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



"Gary''s Student" wrote in message
...
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)
--
Gary''s Student - gsnu200745


"Leonhardtk" wrote:

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

Conditional AVERAGE?
 

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" wrote:

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



"Gary''s Student" wrote in message
...
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)
--
Gary''s Student - gsnu200745


"Leonhardtk" wrote:

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

Conditional AVERAGE?
 

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



"Leonhardtk" wrote in message
...
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

Conditional AVERAGE?
 

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.

"JE McGimpsey" wrote:

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

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


In article ,
Leonhardtk wrote:

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

Conditional AVERAGE?
 

Try

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



--


Regards,


Peo Sjoblom


"Leonhardtk" wrote in message
...
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.

"JE McGimpsey" wrote:

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

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


In article ,
Leonhardtk wrote:

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