| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
| How to swap rows and columns? | Excel Discussion (Misc queries) | |||
| Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
| Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
