| 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| 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) | |||
Linear Mode
