software

We are trying to get an Average between 2 columns that have the same
conditions. We have been using an array formula succesfully on data
that is on 1 column and I have trying to incorporate an Offset into the
formula with no luck.
Here is our latetest attempt, WITHOUT THE OFFSET.
The data is in Column P and Column N

=AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650)))

Thanks advance
Deb--
Deb Pingel
------------------------------------------------------------------------
Deb Pingel's Profile: www.excelforum.com/member.php...oamp;userid=20119
View this thread: www.excelforum.com/showthread...hreadid=498843Try...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3:N6650),3)lt;gt;1,IF((N3:N6650gt;0.01
)*(N3:N6650lt;0.3),IF((P3:P6650gt;0.01)*(P3:P6650lt;0.3) ,N3:P6650))))

....confirmed with CONTROL SHIFT ENTER.

Hope this helps!

In article gt;,
Deb Pingel gt;
wrote:

gt; We are trying to get an Average between 2 columns that have the same
gt; conditions. We have been using an array formula succesfully on data
gt; that is on 1 column and I have trying to incorporate an Offset into the
gt; formula with no luck.
gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; The data is in Column P and Column N
gt;
gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650)))
gt;
gt; Thanks advance
gt; Deb

Or is this what you're looking for?

=AVERAGE(IF((N3:N6650gt;0.01)*(N3:N6650lt;0.3),N3:N665 0,IF((P3:P6650gt;0.01)*(P
3:P6650lt;0.3),P3:P6650)))

....CONTROL SHIFT ENTER.

In article gt;,
Domenic gt; wrote:

gt; Try...
gt;
gt; =AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3:N6650),3)lt;gt;1,IF((N3:N6650gt;0.01
gt; )*(N3:N6650lt;0.3),IF((P3:P6650gt;0.01)*(P3:P6650lt;0.3) ,N3:P6650))))
gt;
gt; ...confirmed with CONTROL SHIFT ENTER.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; Deb Pingel gt;
gt; wrote:
gt;
gt; gt; We are trying to get an Average between 2 columns that have the same
gt; gt; conditions. We have been using an array formula succesfully on data
gt; gt; that is on 1 column and I have trying to incorporate an Offset into the
gt; gt; formula with no luck.
gt; gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; gt; The data is in Column P and Column N
gt; gt;
gt; gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650)))
gt; gt;
gt; gt; Thanks advance
gt; gt; Deb


I tried the suggested formula and it calulates, but I come up with a
completely different number than what I should. I am working on a
seperate sheet just to test the formulas.
Any other suggestions out there?--
Deb Pingel
------------------------------------------------------------------------
Deb Pingel's Profile: www.excelforum.com/member.php...oamp;userid=20119
View this thread: www.excelforum.com/showthread...hreadid=498843Of the two formulas I offered, neither one provided you with the desired
result? If not, can you provide a small sample of your data, about 5
rows, along with your expected result?

In article gt;,
Deb Pingel gt;
wrote:

gt; We are trying to get an Average between 2 columns that have the same
gt; conditions. We have been using an array formula succesfully on data
gt; that is on 1 column and I have trying to incorporate an Offset into the
gt; formula with no luck.
gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; The data is in Column P and Column N
gt;
gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; Data'!P3:P6650)))
gt;
gt; Thanks advance
gt; Deb

Hey Domenic,

How about this, courtesy of Bob:lt;bggt;

=SUM(SUMIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.01 quot;)-SUMIF(INDIRECT({quot;N3:N66
50quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))/SUM(COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.0
1quot;)-COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

quot;Domenicquot; gt; wrote in message
...
gt; Of the two formulas I offered, neither one provided you with the desired
gt; result? If not, can you provide a small sample of your data, about 5
gt; rows, along with your expected result?
gt;
gt; In article gt;,
gt; Deb Pingel gt;
gt; wrote:
gt;
gt; gt; We are trying to get an Average between 2 columns that have the same
gt; gt; conditions. We have been using an array formula succesfully on data
gt; gt; that is on 1 column and I have trying to incorporate an Offset into the
gt; gt; formula with no luck.
gt; gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; gt; The data is in Column P and Column N
gt; gt;
gt; gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; gt; Data'!P3:P6650)))
gt; gt;
gt; gt; Thanks advance
gt; gt; DebYou've got it, RagDyer! I don't know what I was thinking...

In article gt;,
quot;RagDyerquot; gt; wrote:

gt; Hey Domenic,
gt;
gt; How about this, courtesy of Bob:lt;bggt;
gt;
gt; =SUM(SUMIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.01 quot;)-SUMIF(INDIRECT({quot;N3:N66
gt; 50quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))/SUM(COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.0
gt; 1quot;)-COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))
gt;
gt; --
gt; Regards,
gt;
gt; RD

Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3),2)=0,IF(N3:P6650gt;0.01,IF(N3:P
6650lt;0.3,N3:P6650))))

....confirmed with CONTROL SHIFT ENTER.

In article gt;,
quot;RagDyerquot; gt; wrote:

gt; Hey Domenic,
gt;
gt; How about this, courtesy of Bob:lt;bggt;
gt;
gt; =SUM(SUMIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.01 quot;)-SUMIF(INDIRECT({quot;N3:N66
gt; 50quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))/SUM(COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.0
gt; 1quot;)-COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))
gt;
gt; --
gt; Regards,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt;
gt; quot;Domenicquot; gt; wrote in message
gt; ...
gt; gt; Of the two formulas I offered, neither one provided you with the desired
gt; gt; result? If not, can you provide a small sample of your data, about 5
gt; gt; rows, along with your expected result?
gt; gt;
gt; gt; In article gt;,
gt; gt; Deb Pingel gt;
gt; gt; wrote:
gt; gt;
gt; gt; gt; We are trying to get an Average between 2 columns that have the same
gt; gt; gt; conditions. We have been using an array formula succesfully on data
gt; gt; gt; that is on 1 column and I have trying to incorporate an Offset into the
gt; gt; gt; formula with no luck.
gt; gt; gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; gt; gt; The data is in Column P and Column N
gt; gt; gt;
gt; gt; gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; gt; gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650)))
gt; gt; gt;
gt; gt; gt; Thanks advance
gt; gt; gt; Deb

YEP ... and it's less then Half the size too.lt;ggt;

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

quot;Domenicquot; gt; wrote in message
...
Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3),2)=0,IF(N3:P6650gt;0.01,IF(N3:P
6650lt;0.3,N3:P6650))))

....confirmed with CONTROL SHIFT ENTER.

In article gt;,
quot;RagDyerquot; gt; wrote:

gt; Hey Domenic,
gt;
gt; How about this, courtesy of Bob:lt;bggt;
gt;
gt;
=SUM(SUMIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.01 quot;)-SUMIF(INDIRECT({quot;N3:N66
gt;
50quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))/SUM(COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;.0
gt; 1quot;)-COUNTIF(INDIRECT({quot;N3:N6650quot;,quot;P3:P6650quot;}),quot;gt;=.3quot;))
gt;
gt; --
gt; Regards,
gt;
gt; RD
gt;
gt; --------------------------------------------------------------------------
-
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; --------------------------------------------------------------------------
-
gt;
gt; quot;Domenicquot; gt; wrote in message
gt; ...
gt; gt; Of the two formulas I offered, neither one provided you with the desired
gt; gt; result? If not, can you provide a small sample of your data, about 5
gt; gt; rows, along with your expected result?
gt; gt;
gt; gt; In article gt;,
gt; gt; Deb Pingel gt;
gt; gt; wrote:
gt; gt;
gt; gt; gt; We are trying to get an Average between 2 columns that have the same
gt; gt; gt; conditions. We have been using an array formula succesfully on data
gt; gt; gt; that is on 1 column and I have trying to incorporate an Offset into
the
gt; gt; gt; formula with no luck.
gt; gt; gt; Here is our latetest attempt, WITHOUT THE OFFSET.
gt; gt; gt; The data is in Column P and Column N
gt; gt; gt;
gt; gt; gt; =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650gt;.01)*('Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650lt;.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
gt; gt; gt; Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process
gt; gt; gt; Data'!P3:P6650)))
gt; gt; gt;
gt; gt; gt; Thanks advance
gt; gt; gt; Deb

I will be able to try the new formula after lunch, how do you attache a
small samle of data? I tried a jpeg file (as listed in the files drop
down box) and still received an error.--
Deb Pingel
------------------------------------------------------------------------
Deb Pingel's Profile: www.excelforum.com/member.php...oamp;userid=20119
View this thread: www.excelforum.com/showthread...hreadid=498843