Calculating Mode for multiple arrays based on criteria in another

[Guest]

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="SPANISH",Civilian!AA8:AA9="SPANISH",Marines!AA8:AA17="SPANISH",Navy!AA8:AA43="SPANISH"),MODE(AirForce!AB8:AB412,Army!AB8:AB52,Civilian!AB8:AB9,Marines!AB8:AB17,Navy!AB8:AB43),"")

 

[Domenic]

If you download and install the free add-in Morefunc.xll, you can use
the following formula...

=MODE(IF(THREED('AirForce:Navy'!AA8:AA500)="Spanish",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&"'!AB8: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!

 

[Guest]

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]

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!