ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Averages from 2 columns (https://www.excelbanter.com/excel-worksheet-functions/63659-averages-2-columns.html)

Deb Pingel
Averages from 2 columns
 

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:P6650.01)*('Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650<.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: http://www.excelforum.com/member.php...o&userid=20119
View this thread: http://www.excelforum.com/showthread...hreadid=498843

Domenic
Averages from 2 columns
 
Try...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3:N6650),3)<1,IF((N3:N66500.01
)*(N3:N6650<0.3),IF((P3:P66500.01)*(P3:P6650<0.3) ,N3:P6650))))

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

Hope this helps!

In article ,
Deb Pingel
wrote:

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

Domenic
Averages from 2 columns
 
Or is this what you're looking for?

=AVERAGE(IF((N3:N66500.01)*(N3:N6650<0.3),N3:N665 0,IF((P3:P66500.01)*(P
3:P6650<0.3),P3:P6650)))

....CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Try...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3:N6650),3)<1,IF((N3:N66500.01
)*(N3:N6650<0.3),IF((P3:P66500.01)*(P3:P6650<0.3) ,N3:P6650))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Deb Pingel
wrote:

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:P6650.01)*('Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650<.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
Averages from 2 columns
 

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: http://www.excelforum.com/member.php...o&userid=20119
View this thread: http://www.excelforum.com/showthread...hreadid=498843

Domenic
Averages from 2 columns
 
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?

In article ,
Deb Pingel
wrote:

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

RagDyer
Averages from 2 columns
 
Hey Domenic,

How about this, courtesy of Bob:<bg

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

--
Regards,

RD

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

"Domenic" wrote in message
...
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?

In article ,
Deb Pingel
wrote:

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

Domenic
Averages from 2 columns
 
You've got it, RagDyer! I don't know what I was thinking... :)

In article ,
"RagDyer" wrote:

Hey Domenic,

How about this, courtesy of Bob:<bg

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

--
Regards,

RD

Domenic
Averages from 2 columns
 
Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3),2)=0,IF(N3:P66500.01,IF(N3:P
6650<0.3,N3:P6650))))

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

In article ,
"RagDyer" wrote:

Hey Domenic,

How about this, courtesy of Bob:<bg

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

--
Regards,

RD

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

"Domenic" wrote in message
...
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?

In article ,
Deb Pingel
wrote:

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

RagDyeR
Averages from 2 columns
 
YEP ... and it's less then Half the size too.<g

--

Regards,

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

"Domenic" wrote in message
...
Here's another alternative...

=AVERAGE(IF(MOD(COLUMN(N3:P6650)-COLUMN(N3),2)=0,IF(N3:P66500.01,IF(N3:P
6650<0.3,N3:P6650))))

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

In article ,
"RagDyer" wrote:

Hey Domenic,

How about this, courtesy of Bob:<bg


=SUM(SUMIF(INDIRECT({"N3:N6650","P3:P6650"}),".01 ")-SUMIF(INDIRECT({"N3:N66

50","P3:P6650"}),"=.3"))/SUM(COUNTIF(INDIRECT({"N3:N6650","P3:P6650"}),".0
1")-COUNTIF(INDIRECT({"N3:N6650","P3:P6650"}),"=.3"))

--
Regards,

RD

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

"Domenic" wrote in message
...
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?

In article ,
Deb Pingel
wrote:

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:P6650.01)*('Mill Process Data'!N3:N6650,'Mill Process
Data'!P3:P6650<.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
Averages from 2 columns
 

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: http://www.excelforum.com/member.php...o&userid=20119
View this thread: http://www.excelforum.com/showthread...hreadid=498843

Domenic
Averages from 2 columns
 

Deb Pingel Wrote:
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'.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=498843


All times are GMT +1. The time now is 02:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com