Selecting a Range

Another one:

=sumif(c17:c2000,"sar",o17:o2000)/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 wrote:
>
> =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?

--

Dave Peterson

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.

On Tue, 26 Aug 2008 18:10:31 -0500, Dave Peterson
<petersod RemoveThis @verizonXSPAM.net> wrote:

>Another one:
>
>=sumif(c17:c2000,"sar",o17:o2000)/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 wrote:
>>
>> =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?

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
---
"JimS" <jimx22.RemoveThis@msn.com> wrote in message
news:stv8b4lr7gpl0gg0ke3ufs73t1qdegii3m@4ax.com...
> =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?

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.

On Wed, 27 Aug 2008 06:59:37 +0800, "Max" <demechanik.RemoveThis@yahoo.com>
wrote:

>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)))

Got it.

Entered as array:

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

On Tue, 26 Aug 2008 21:08:54 -0700, JimS <jimx22.DeleteThis@msn.com> wrote:

>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.
>
>On Wed, 27 Aug 2008 06:59:37 +0800, "Max" <demechanik.DeleteThis@yahoo.com>
>wrote:
>
>>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)))

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" <jimx22.DeleteThis@msn.com> wrote in message
news:ckk9b4pnl0crm4ck94u7tg2cg9gvsv25ve@4ax.com...
>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.