Thread: Help cleaning up formula

Hello all,

I need some help cleaning up the formula below to reduce the amount of time it takes to compute. So far, the workbook takes about 5 minutes to go to the whole equation as I have it repeated about 1050 times in different cells.

Perhaps a macro would expedite the calculations, but I have no knowledge of how to do one to reproduce what I want to do.

Formula:

=(SUM(('Raw Data 14-26'!$C$1:'Raw Data 14-26'!$C$64635='Raw Data 14-26'!$P$2)*('Raw Data 14-26'!$B$1:'Raw Data 14-26'!$B$64635=1)*(IF(ISTEXT('Raw Data 14-26'!$G$1:'Raw Data 14-26'!$G$64635),0,'Raw Data 14-26'!$G$1:'Raw Data 14-26'!$G$64635))))*-1

I am simply doing a 3 array multiplication with ranges of 6 colums by 64635 rows and then adding the resulting array to come up with a value specific to a cell. The problem comes with the imported data as it has text and numbers and thus the use of the is text function and if statements. In a graphical example, this is what I want to do.

{0 0 34}*{0 0 1}*{0 0 1}={0 0 34}=sum(0 0 43}=34 but in a much bigger array.

I'm also using the following mode formula to automatically obtain some of the values that I'm looking in the previous formula.

Mode formula:

=IF(ISNA(MODE(IF(COUNTIF(P$1:P2,$C$47:$C$486)=0,$C$47:$C$486))),0,MODE(IF(COUNTIF(P$1:P2,$C$47:$C$48 6)=0,$C$47:$C$486)))

I attached the workbook in case someone want to give it a stab. I thank in advance to anyone who could help me.

Those ranges are way too big... for all the formulas you are trying to calculate...
You need to shorten the ranges... maybe make them dynamic...

For example, in B1 of Points 1-13 sheet enter formula: =MATCH(9.9999999999E+307,'Raw Data 1-13'!B:B)

That finds the last row in the Raw Data 1-13 sheet with a number in column B... assuming that the last row will have a number...

then, say in F6 of Points 1-13 you can use formula like:

Code:

=-SUMPRODUCT(('Raw Data 1-13'!$C$1:INDEX('Raw Data 1-13'!$C:$C,$B$1)='Raw Data 1-13'!$P$3)*('Raw Data 1-13'!$B$1:INDEX('Raw Data 1-13'!$B:$B,$B$1)=COLUMNS($E$1:E$1))*(ISNUMBER('Raw Data 1-13'!$G$1:INDEX('Raw Data 1-13'!$G:$G,$B$1))),'Raw Data 1-13'!$G$1:INDEX('Raw Data 1-13'!$G:$G,$B$1))

and copy it across the 9 columns.. note the COLUMNS($E$1:E$1) in the formula is what adjusts the number to compare too from 1 to 2 to 3 to 4, etc...

You can use this same concept on the other tables/cells...