R
rtilghman
So a few days ago I posted regarding a script I was trying to get
working to give me two-part analysis of cells in a sheet. Bob
Phillips posted with a working script that used the ISNUMBER/FIND
method for doing the same thing as using straight arrays (which I was
doing previously).
The solution is great, but I have one lingering problem; I'd really
like the ability to do this same calculation three dimensionally
(multiple sheets) as opposed to two dimensionally (single sheet).
FIND, as I've discovered in my brief Excel ramp-up, only works in two-
dimenions in formulas...
While I could pull all the results I need into a single sheet, I'm
dealing with 66 interviews that have 45 questions each, and the amount
of data I would need to aggregate to one sheet to do it would be
enormous.
So, is there any way to make the following formula three dimensional?
=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))*
(ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"")
If anyone has any thoughts I'd appreciate. I'd like to do this in
formula if possible, but any ideas or insights are welcome.
Thanks,
Rick
working to give me two-part analysis of cells in a sheet. Bob
Phillips posted with a working script that used the ISNUMBER/FIND
method for doing the same thing as using straight arrays (which I was
doing previously).
The solution is great, but I have one lingering problem; I'd really
like the ability to do this same calculation three dimensionally
(multiple sheets) as opposed to two dimensionally (single sheet).
FIND, as I've discovered in my brief Excel ramp-up, only works in two-
dimenions in formulas...
While I could pull all the results I need into a single sheet, I'm
dealing with 66 interviews that have 45 questions each, and the amount
of data I would need to aggregate to one sheet to do it would be
enormous.
So, is there any way to make the following formula three dimensional?
=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))*
(ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"")
If anyone has any thoughts I'd appreciate. I'd like to do this in
formula if possible, but any ideas or insights are welcome.
Thanks,
Rick