Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Mode for multiple arrays based on criteria in another
Help! I have a workbook and am trying to calculate Mode (and other averages)
for arrays in one column from multiple worksheets containing scores (Column AB) based on meeting a condition (having a text value=Spanish, for example) in an array (Column AA) from multiple worksheets. I was using the following function, but cannot even get to the F2 and ctrl+shift+enter due to Excel telling me there is an error in my function. Can anyone please help me debug why or how to do this? Here is the function I am currently using: =IF(OR(AirForce!AA8:412="SPANISH",Army!AA8:AA52="S PANISH",Civilian!AA8:AA9="SPANISH",Marines!AA8:AA1 7="SPANISH",Navy!AA8:AA43="SPANISH"),MODE(AirForce !AB8:AB412,Army!AB8:AB52,Civilian!AB8:AB9,Marines! AB8:AB17,Navy!AB8:AB43),"") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Mode for multiple arrays based on criteria in another
If you download and install the free add-in Morefunc.xll, you can use
the following formula... =MODE(IF(THREED('AirForce:Navy'!AA8:AA500)="Spanis h",THREED('AirForce:Nav y'!AB8:AB500))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Otherwise you can use this rather expensive and inefficient formula... =MODE(IF(T(OFFSET(INDIRECT("'"&A1:E1&"'!AA8:AA500" ),ROW(INDIRECT("8:500") )-8,0,1))="Spanish",N(OFFSET(INDIRECT("'"&A1:E1&"'!A B8:AB500"),ROW(INDIRE CT("8:500"))-8,0,1)))) ....where A1:E1 contains the sheet names. This formula also needs to be confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges accordingly. Note that the add-in can be downloaded here... http://xcell05.free.fr/ Hope this helps! In article , AngelaMaria wrote: Help! I have a workbook and am trying to calculate Mode (and other averages) for arrays in one column from multiple worksheets containing scores (Column AB) based on meeting a condition (having a text value=Spanish, for example) in an array (Column AA) from multiple worksheets. I was using the following function, but cannot even get to the F2 and ctrl+shift+enter due to Excel telling me there is an error in my function. Can anyone please help me debug why or how to do this? Here is the function I am currently using: =IF(OR(AirForce!AA8:412="SPANISH",Army!AA8:AA52="S PANISH",Civilian!AA8:AA9="SP ANISH",Marines!AA8:AA17="SPANISH",Navy!AA8:AA43="S PANISH"),MODE(AirForce!AB8:A B412,Army!AB8:AB52,Civilian!AB8:AB9,Marines!AB8:AB 17,Navy!AB8:AB43),"") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Mode for multiple arrays based on criteria in anot
Dominic,
First, thank you for the help. However, I am somewhat new to Excel and am only at the amateur level. I did not understand OFFSET, INDIRECT, or ROW functions. Is there someway to troubleshoot the function/formula I was trying (as I understand the logic in that case)? I was hoping I had left something out or put it in the wrong order. Thank you! "Domenic" wrote: If you download and install the free add-in Morefunc.xll, you can use the following formula... =MODE(IF(THREED('AirForce:Navy'!AA8:AA500)="Spanis h",THREED('AirForce:Nav y'!AB8:AB500))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Otherwise you can use this rather expensive and inefficient formula... =MODE(IF(T(OFFSET(INDIRECT("'"&A1:E1&"'!AA8:AA500" ),ROW(INDIRECT("8:500") )-8,0,1))="Spanish",N(OFFSET(INDIRECT("'"&A1:E1&"'!A B8:AB500"),ROW(INDIRE CT("8:500"))-8,0,1)))) ....where A1:E1 contains the sheet names. This formula also needs to be confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges accordingly. Note that the add-in can be downloaded here... http://xcell05.free.fr/ Hope this helps! In article , AngelaMaria wrote: Help! I have a workbook and am trying to calculate Mode (and other averages) for arrays in one column from multiple worksheets containing scores (Column AB) based on meeting a condition (having a text value=Spanish, for example) in an array (Column AA) from multiple worksheets. I was using the following function, but cannot even get to the F2 and ctrl+shift+enter due to Excel telling me there is an error in my function. Can anyone please help me debug why or how to do this? Here is the function I am currently using: =IF(OR(AirForce!AA8:412="SPANISH",Army!AA8:AA52="S PANISH",Civilian!AA8:AA9="SP ANISH",Marines!AA8:AA17="SPANISH",Navy!AA8:AA43="S PANISH"),MODE(AirForce!AB8:A B412,Army!AB8:AB52,Civilian!AB8:AB9,Marines!AB8:AB 17,Navy!AB8:AB43),"") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Mode for multiple arrays based on criteria in anot
First, if at all possible, I would strongly suggest you use the first
approach. It's much more efficient. Having said that... 1) Make sure that the sheet names are listed in a 'horizontal' range of cells. In the example I provided, the sheet names were listed in A1:E1. The formula won't work if, for example, the sheet names were listed in a vertical range of cells, such as A1:A5. 2) Make sure that the formula is confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, after typing the formula, hold the CONTROL and SHIFT keys down, then while both these keys are pressed down, press the ENTER key. Excel will automatically place braces { } around the formula, indicating that you've entered the formula correctly. If you continue to have problems, post the exact formula you're using... Hope this helps! In article , AngelaMaria wrote: Dominic, First, thank you for the help. However, I am somewhat new to Excel and am only at the amateur level. I did not understand OFFSET, INDIRECT, or ROW functions. Is there someway to troubleshoot the function/formula I was trying (as I understand the logic in that case)? I was hoping I had left something out or put it in the wrong order. Thank you! "Domenic" wrote: If you download and install the free add-in Morefunc.xll, you can use the following formula... =MODE(IF(THREED('AirForce:Navy'!AA8:AA500)="Spanis h",THREED('AirForce:Nav y'!AB8:AB500))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Otherwise you can use this rather expensive and inefficient formula... =MODE(IF(T(OFFSET(INDIRECT("'"&A1:E1&"'!AA8:AA500" ),ROW(INDIRECT("8:500") )-8,0,1))="Spanish",N(OFFSET(INDIRECT("'"&A1:E1&"'!A B8:AB500"),ROW(INDIRE CT("8:500"))-8,0,1)))) ....where A1:E1 contains the sheet names. This formula also needs to be confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges accordingly. Note that the add-in can be downloaded here... http://xcell05.free.fr/ Hope this helps! In article , AngelaMaria wrote: Help! I have a workbook and am trying to calculate Mode (and other averages) for arrays in one column from multiple worksheets containing scores (Column AB) based on meeting a condition (having a text value=Spanish, for example) in an array (Column AA) from multiple worksheets. I was using the following function, but cannot even get to the F2 and ctrl+shift+enter due to Excel telling me there is an error in my function. Can anyone please help me debug why or how to do this? Here is the function I am currently using: =IF(OR(AirForce!AA8:412="SPANISH",Army!AA8:AA52="S PANISH",Civilian!AA8:AA9 ="SP ANISH",Marines!AA8:AA17="SPANISH",Navy!AA8:AA43="S PANISH"),MODE(AirForce!A B8:A B412,Army!AB8:AB52,Civilian!AB8:AB9,Marines!AB8:AB 17,Navy!AB8:AB43),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Mode Function with 2 Criteria | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |