Drag down range formula (skipping one row)

**thaphthia** · 04-08-2013, 09:39 AM

Hi everyone,

I'm looking to create a formula that calculates the average of a particular series of cells (J26-J50) if the corresponding value in column K says "True", however I don't want to count one row in the average (row 37).

Will the following formula work? I used colons to continue the series that I am looking to average, but I'm not sure if that's right.
=AVERAGEIF($K$26:$K$36:$K$38:$K$50,"TRUE", $J$26:$J$36:$J$38:$J$50)

Thanks in advance!

**ChemistB** · 04-08-2013, 10:24 AM

This Arrayed function will work. (use CNTRL SHFT ENTER instead of ENTER. You should see brackets {} around formula if done properly)

=AVERAGE(IF($K$26:$K$50,IF(ROW($J$26:$J$50)<>37,$J$26:$J$50,"")))
Does that work for you?

**thaphthia** · 04-08-2013, 10:56 AM

Yes, that formula works well! Thank you very much.

I'm wondering if there is a simple way to do this, I have several other formulas that need to skip the same row as well (row 37):

=STDEV(IF($H$26:$H$36:$H$38:$H$50="J",$J$26:$J$36:$J$38:$J$50))

=COUNTIF($H$26:$H$36:$H$38:$H$50,"="&"J")

=AVERAGEIFS($J$26:$J$36:$J$38:$J$50,$J$26:$J$36:$J$38:$J$50,">="&(D110-(2*D109)),$J$26:$J$36:$J$38:$J$50,"<="&(D110+(2*D109)))

Is there an easy way to just replace the colon I was attempting with something simple that will help me skip that row?

Thanks again!

**ChemistB** · 04-08-2013, 11:53 AM

I don't believe there is. The "IFS" functions are limited as to what criteria you can use. You could use SUMPRODUCT which won't require making it an array but that would be more complex than the formula I gave you..

You could use a dummy column to ignore row 37, Let's say Column M isn't used
In M37, put an x
=AVERAGEIFS($J$26:$J$50, $K$26:$K$50, "TRUE", $M$26:$M$50, "<>x")
Is that easier for you?