Averages from 2 columns

[Deb Pingel]

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'!P36650>.01)*('Mill Process Data'!N3:N6650,'Mill Process
Data'!P36650<.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill
Process Data'!P36650)),Mill Process Data'!N3:N6650,'Mill Process
Data'!P36650)))

Thanks advance
Deb

 

[Domenic]

Try...

=AVERAGE(IF(MOD(COLUMN(N36650)-COLUMN(N3:N6650),3)<>1,IF((N3:N6650>0.01
)*(N3:N6650<0.3),IF((P36650>0.01)*(P36650<0.3),N36650))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Domenic]

Or is this what you're looking for?

=AVERAGE(IF((N3:N6650>0.01)*(N3:N6650<0.3),N3:N6650,IF((P36650>0.01)*(P
36650<0.3),P36650)))

....CONTROL+SHIFT+ENTER.

 

[Deb Pingel]

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?

 

[Domenic]

Of 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?

 

[RagDyer]

Hey Domenic,

How about this, courtesy of Bob:<bg>

=SUM(SUMIF(INDIRECT({"N3:N6650","P36650"}),">.01")-SUMIF(INDIRECT({"N3:N66
50","P36650"}),">=.3"))/SUM(COUNTIF(INDIRECT({"N3:N6650","P36650"}),">.0
1")-COUNTIF(INDIRECT({"N3:N6650","P36650"}),">=.3"))

 

[Domenic]

You've got it, RagDyer! I don't know what I was thinking...

 

[Domenic]

Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N36650)-COLUMN(N3),2)=0,IF(N36650>0.01,IF(N3
6650<0.3,N36650))))

....confirmed with CONTROL+SHIFT+ENTER.

 

[RagDyeR]

YEP ... and it's less then Half the size too.<g>

--

Regards,

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

Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N36650)-COLUMN(N3),2)=0,IF(N36650>0.01,IF(N3
6650<0.3,N36650))))

....confirmed with CONTROL+SHIFT+ENTER.

 

[Deb Pingel]

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.

 

[Domenic]

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.

After typing your message, scroll down the page and click on 'Manage
Attachments'. Then, click on 'Choose File', click 'Upload', and then
click on 'Close this window'.