Selecting a Range

[JimS]

=IF(ISERROR(AVERAGE(O17:O2013)),0,(AVERAGE(O17:O2002)))

I have this formula in a cell but I want to add a condition. I want
it to average the data in question but only if:

c17:c2000="sar"

Whats the easiest way to insert that in there?

 

[Don Guillett]

This is an ARRAY formula that must be entered using ctrl+shift+enter AND the
ranges must be the same size.
Don't use 2000 in one 2002 in another and 2013 in a third

=AVERAGE(IF(c17:c2000="sar",o172000))

 

[Max]

The core expression, array-entered*:
=AVERAGE(IF(C17:C2002="sar",O17:O2002))

*press CTRL+SHIFT+ENTER to confirm the formula

With the error trap, array-entered*:
=IF(ISERROR(AVERAGE(IF(C17:C2002="sar",O17:O2002))),0,AVERAGE(IF(C17:C2002="sar",O17:O2002)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Dave Peterson]

Another one:

=sumif(c17:c2000,"sar",o172000)/max(1,countif(c17:c2000,"sar"))

I figured you were returning 0 if there were numbers to average. And if there
are no numbers to average, then the =sumif() portion would be 0. But so would
the =countif(). So I made sure I had a non-zero denominator using the =max()
function.

And watch your ranges. I would expect them to be the same number of rows.

 

[JimS]

Thanks you guys. I don't know why my ranges change like that. I put
2000 in all of them, but for some reason they move around.

 

[JimS]

I have one more question about this formula. In column C I have the
abbreviated names of places

bel
sar
emd

etc.

How could I make this formula work with whatever I put into cell L2?
In other words cell L2 has "sar" so that's what I want to average. If
I change cell L2 it to emd then it looks for emd and averages that.

 

[mikerickson]

Try
=IF(COUNTIF(C17:C2000,L2), SUMIF(C17:C2000,L2,O17:O2000)/COUNTIF(C17:C2000,L2), 0)

(not an array formula)

 

[Max]

To point to a cell, L2,
which will contain the text: sar (w/o the double quotes)

The 2 array-entered expressions will be simply:

=AVERAGE(IF(C17:C2002=L2,O17:O2002))

=IF(ISERROR(AVERAGE(IF(C17:C2002=L2,O17:O2002))),0,
AVERAGE(IF(C17:C2002=L2,O17:O2002)))

And if you have a list of text entered in L2 down (ie in L2, L3, L4, etc)
just fix the ranges like this in your start cell, say in M2:

=AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002))

=IF(ISERROR(AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002))),0,
AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002)))

Then just copy M2 down as far as required
to return the corresponding results for each value in L2, l3, L4 etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[JimS]

Got it.

Entered as array:

=IF(ISERROR(AVERAGE(IF(C17:C2002=L2,O17:O2002))),0,AVERAGE(IF(C17:C2002=L2,O17:O2002)))

 

[JimS]

OK, I'm stumped. I've pulled my hair out for two hours, so I give up.
First of all thank everyone for the help.

I shifted everything to the left so the values have changed slightly.

I can't get this to work for a column of inputed data. It works fine
for a column of data derived from formulas. That's the only thing I
can think of that's holding me up. Here is the formula that works:


=IF(ISERROR(AVERAGE(IF(C15:C2498=K2,O15:O2498))),0,AVERAGE(IF(C15:C2498=K2,O15:O2498)))

This one doesn't work:

=IF(ISERROR(AVERAGE(IF(C15:C2498=K2,N15:N2498))),0,AVERAGE(IF(C15:C2498=K2,N15:N2498)))

I'm trying to do the same thing for two different columns of data.
The problem is that the second formula averages when there is no value
in the cell. So if my column is:

$10.00
$10.00
(blank cell)

I get an answer of $6.67 when I want an answer of $10.00. I want the
average of the actual values only.

Like I said the first formula does what I want no problem, and there
really shouldn't be any difference that I can see.

 

[Max]

My guess is that you need an additional ISNUMBER check on the col N to be
averaged, to ensure that the average will be only for the cells in col N
that contains numbers (besides the concurrent check on col C = K2):

Try, array-entered:
=IF(ISERROR(AVERAGE(IF((C15:C2498=K2)*(ISNUMBER(N15:N2498)),N15:N2498))),0,AVERAGE(IF((C15:C2498=K2)*(ISNUMBER(N15:N2498)),N15:N2498)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[JimS]

Thank you so much! That did it. I never would have got it.

I really appreciate it.

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik