There must be an easier way...

[elfishio]

I put together a pretty bulky formula to compute weighted averages from a max of 40 data points that are not next to each other (but in some sort of sequential order), excluding data points that either show up as an error or zero. The formula is currently 8000+ characters, and awfully close to the max formula space. I have inserted it below, maybe someone can help me figure out a more concise way of putting this together.
There is a grid on 'sel' that determines if a data point will be used in the formula.
Also the formula has the ability to be spread out without losing functionality.
The question is how can i employ a macro to expedite this, or

=((IF(sel!$C$60=TRUE,(IF(IFERROR(trends!I196,TRUE)=TRUE,,if(trends!I196=0,,(trends!I196*trends!I$23))))))+
(IF(sel!$C$61=TRUE,(IF(IFERROR(trends!O196,TRUE)=TRUE,,if(trends!O196=0,,(trends!O196*trends!O$23))))))+
(IF(sel!$C$62=TRUE,(IF(IFERROR(trends!U196,TRUE)=TRUE,,if(trends!U196=0,,(trends!U196*trends!U$23))))))+
(IF(sel!$C$63=TRUE,(IF(IFERROR(trends!AA196,TRUE)=TRUE,,if(trends!AA196=0,,(trends!AA196*trends!AA$23))))))+
(IF(sel!$C$64=TRUE,(IF(IFERROR(trends!AG196,TRUE)=TRUE,,if(trends!AG196=0,,(trends!AG196*trends!AG$23))))))+
(IF(sel!$C$65=TRUE,(IF(IFERROR(trends!AM196,TRUE)=TRUE,,if(trends!AM196=0,,(trends!AM196*trends!AM$23))))))+
(IF(sel!$C$66=TRUE,(IF(IFERROR(trends!AS196,TRUE)=TRUE,,if(trends!AS196=0,,(trends!AS196*trends!AS$23))))))+
(IF(sel!$C$67=TRUE,(IF(IFERROR(trends!AY196,TRUE)=TRUE,,if(trends!AY196=0,,(trends!AY196*trends!AY$23))))))+
(IF(sel!$C$68=TRUE,(IF(IFERROR(trends!BE196,TRUE)=TRUE,,if(trends!BE196=0,,(trends!BE196*trends!BE$23))))))+
(IF(sel!$C$69=TRUE,(IF(IFERROR(trends!BK196,TRUE)=TRUE,,if(trends!BK196=0,,(trends!BK196*trends!BK$23))))))+
(IF(sel!$C$70=TRUE,(IF(IFERROR(trends!BQ196,TRUE)=TRUE,,if(trends!BQ196=0,,(trends!BQ196*trends!BQ$23))))))+
(IF(sel!$C$71=TRUE,(IF(IFERROR(trends!BW196,TRUE)=TRUE,,if(trends!BW196=0,,(trends!BW196*trends!BW$23))))))+
(IF(sel!$C$72=TRUE,(IF(IFERROR(trends!CC196,TRUE)=TRUE,,if(trends!CC196=0,,(trends!CC196*trends!CC$23))))))+
(IF(sel!$C$73=TRUE,(IF(IFERROR(trends!CI196,TRUE)=TRUE,,if(trends!CI196=0,,(trends!CI196*trends!CI$23))))))+
(IF(sel!$C$74=TRUE,(IF(IFERROR(trends!CO196,TRUE)=TRUE,,if(trends!CO196=0,,(trends!CO196*trends!CO$23))))))+
(IF(sel!$C$75=TRUE,(IF(IFERROR(trends!CU196,TRUE)=TRUE,,if(trends!CU196=0,,(trends!CU196*trends!CU$23))))))+
(IF(sel!$C$76=TRUE,(IF(IFERROR(trends!DA196,TRUE)=TRUE,,if(trends!DA196=0,,(trends!DA196*trends!DA$23))))))+
(IF(sel!$C$77=TRUE,(IF(IFERROR(trends!DG196,TRUE)=TRUE,,if(trends!DG196=0,,(trends!DG196*trends!DG$23))))))+
(IF(sel!$C$78=TRUE,(IF(IFERROR(trends!DM196,TRUE)=TRUE,,if(trends!DM196=0,,(trends!DM196*trends!DM$23))))))+
(IF(sel!$C$79=TRUE,(IF(IFERROR(trends!DS196,TRUE)=TRUE,,if(trends!DS196=0,,(trends!DS196*trends!DS$23))))))+
(IF(sel!$C$80=TRUE,(IF(IFERROR(trends!DY196,TRUE)=TRUE,,if(trends!DY196=0,,(trends!DYI196*trends!DY$23))))))+
(IF(sel!$C$81=TRUE,(IF(IFERROR(trends!EE196,TRUE)=TRUE,,if(trends!EE196=0,,(trends!EE196*trends!EE$23))))))+
(IF(sel!$C$82=TRUE,(IF(IFERROR(trends!EK196,TRUE)=TRUE,,if(trends!EK196=0,,(trends!EK196*trends!EK$23))))))+
(IF(sel!$C$83=TRUE,(IF(IFERROR(trends!EQ196,TRUE)=TRUE,,if(trends!EQ196=0,,(trends!EQ196*trends!EQ$23))))))+
(IF(sel!$C$84=TRUE,(IF(IFERROR(trends!EW196,TRUE)=TRUE,,if(trends!EW196=0,,(trends!EW196*trends!EW$23))))))+
(IF(sel!$C$85=TRUE,(IF(IFERROR(trends!FC196,TRUE)=TRUE,,if(trends!FC196=0,,(trends!FC196*trends!FC$23))))))+
(IF(sel!$C$86=TRUE,(IF(IFERROR(trends!FI196,TRUE)=TRUE,,if(trends!FI196=0,,(trends!FI196*trends!FI$23))))))+
(IF(sel!$C$87=TRUE,(IF(IFERROR(trends!FO196,TRUE)=TRUE,,if(trends!FO196=0,,(trends!FO196*trends!FO$23))))))+
(IF(sel!$C$88=TRUE,(IF(IFERROR(trends!FU196,TRUE)=TRUE,,if(trends!FU196=0,,(trends!FU196*trends!FU$23))))))+
(IF(sel!$C$89=TRUE,(IF(IFERROR(trends!GA196,TRUE)=TRUE,,if(trends!GA196=0,,(trends!GA196*trends!GA$23))))))+
(IF(sel!$C$90=TRUE,(IF(IFERROR(trends!GG196,TRUE)=TRUE,,if(trends!GG196=0,,(trends!GG196*trends!GG$23))))))+
(IF(sel!$C$91=TRUE,(IF(IFERROR(trends!GM196,TRUE)=TRUE,,if(trends!GM196=0,,(trends!GM196*trends!GM$23))))))+
(IF(sel!$C$92=TRUE,(IF(IFERROR(trends!GS196,TRUE)=TRUE,,if(trends!GS196=0,,(trends!GS196*trends!GS$23))))))+
(IF(sel!$C$93=TRUE,(IF(IFERROR(trends!GY196,TRUE)=TRUE,,if(trends!GY196=0,,(trends!GY196*trends!GY$23))))))+
(IF(sel!$C$94=TRUE,(IF(IFERROR(trends!HE196,TRUE)=TRUE,,if(trends!HE196=0,,(trends!HE196*trends!HE$23))))))+
(IF(sel!$C$95=TRUE,(IF(IFERROR(trends!HK196,TRUE)=TRUE,,if(trends!HK196=0,,(trends!HK196*trends!HK$23))))))+
(IF(sel!$C$96=TRUE,(IF(IFERROR(trends!HQ196,TRUE)=TRUE,,if(trends!HQ196=0,,(trends!HQ196*trends!HQ$23))))))+
(IF(sel!$C$97=TRUE,(IF(IFERROR(trends!HW196,TRUE)=TRUE,,if(trends!HW196=0,,(trends!HW196*trends!HW$23))))))+
(IF(sel!$C$98=TRUE,(IF(IFERROR(trends!IC196,TRUE)=TRUE,,if(trends!IC196=0,,(trends!IC196*trends!I$23))))))+
(IF(sel!$C$99=TRUE,(IF(IFERROR(trends!II196,TRUE)=TRUE,,if(trends!II196=0,,(trends!II196*trends!I$23)))))))
/
SUM(IF(sel!$C$60=TRUE,IF(IFERROR(trends!I196,TRUE)=TRUE,,if(trends!I196=0,,trends!I$23)),),
IF(sel!$C$61=TRUe,IF(IFERROR(trends!O196,TRUE)=TRUE,,if(trends!O196=0,,trends!O$23)),),
IF(sel!$C$62=TRUE,IF(IFERROR(trends!U196,TRUE)=TRUE,,if(trends!U196=0,,trends!U$23)),),
IF(sel!$C$63=TRUE,IF(IFERROR(trends!AA196,TRUE)=TRUE,,if(trends!AA196=0,,trends!AA$23)),),
IF(sel!$C$64=TRUE,IF(IFERROR(trends!AG196,TRUE)=TRUE,,if(trends!AG196=0,,trends!AG$23)),),
IF(sel!$C$65=TRUE,IF(IFERROR(trends!AM196,TRUE)=TRUE,,if(trends!AM196=0,,trends!AM$23)),),
IF(sel!$C$66=TRUE,IF(IFERROR(trends!AS196,TRUE)=TRUE,,if(trends!AS196=0,,trends!AS$23)),),
IF(sel!$C$67=TRUE,IF(IFERROR(trends!AY196,TRUE)=TRUE,,if(trends!AY196=0,,trends!AY$23)),),
IF(sel!$C$68=TRUE,IF(IFERROR(trends!BE196,TRUE)=TRUE,,if(trends!BE196=0,,trends!BE$23)),),
IF(sel!$C$69=TRUE,IF(IFERROR(trends!BK196,TRUE)=TRUE,,if(trends!BK196=0,,trends!BK$23)),),
IF(sel!$C$70=TRUE,IF(IFERROR(trends!BQ196,TRUE)=TRUE,,if(trends!BE196=0,,trends!BQ$23)),),
IF(sel!$C$71=TRUE,IF(IFERROR(trends!BW196,TRUE)=TRUE,,if(trends!BQ196=0,,trends!BW$23)),),
IF(sel!$C$72=TRUE,IF(IFERROR(trends!CC196,TRUE)=TRUE,,if(trends!CC196=0,,trends!CC$23)),),
IF(sel!$C$73=TRUE,IF(IFERROR(trends!CI196,TRUE)=TRUE,,if(trends!CI196=0,,trends!CI$23)),),
IF(sel!$C$74=TRUE,IF(IFERROR(trends!CO196,TRUE)=TRUE,,if(trends!CO196=0,,trends!CO$23)),),
IF(sel!$C$75=TRUE,IF(IFERROR(trends!CU196,TRUE)=TRUE,,if(trends!CU196=0,,trends!CU$23)),),
IF(sel!$C$76=TRUE,IF(IFERROR(trends!DA196,TRUE)=TRUE,,if(trends!DA196=0,,trends!DA$23)),),
IF(sel!$C$77=TRUE,IF(IFERROR(trends!DG196,TRUE)=TRUE,,if(trends!DG196=0,,trends!DG$23)),),
IF(sel!$C$78=TRUE,IF(IFERROR(trends!DM196,TRUE)=TRUE,,if(trends!DM196=0,,trends!DM$23)),),
IF(sel!$C$79=TRUE,IF(IFERROR(trends!DS196,TRUE)=TRUE,,if(trends!DS196=0,,trends!DS$23)),),
IF(sel!$C$80=TRUE,IF(IFERROR(trends!DY196,TRUE)=TRUE,,if(trends!DY196=0,,trends!DY$23)),),
IF(sel!$C$81=TRUE,IF(IFERROR(trends!EE196,TRUE)=TRUE,,if(trends!EE196=0,,trends!EE$23)),),
IF(sel!$C$82=TRUE,IF(IFERROR(trends!EK196,TRUE)=TRUE,,if(trends!EK196=0,,trends!EK$23)),),
IF(sel!$C$83=TRUE,IF(IFERROR(trends!EQ196,TRUE)=TRUE,,if(trends!EQ196=0,,trends!EQ$23)),),
IF(sel!$C$84=TRUE,IF(IFERROR(trends!EW196,TRUE)=TRUE,,if(trends!EW196=0,,trends!EW$23)),),
IF(sel!$C$85=TRUE,IF(IFERROR(trends!FC196,TRUE)=TRUE,,if(trends!FC196=0,,trends!FC$23)),),
IF(sel!$C$86=TRUE,IF(IFERROR(trends!FI196,TRUE)=TRUE,,if(trends!FI196=0,,trends!FI$23)),),
IF(sel!$C$87=TRUE,IF(IFERROR(trends!FO196,TRUE)=TRUE,,if(trends!FO196=0,,trends!FO$23)),),
IF(sel!$C$88=TRUE,IF(IFERROR(trends!FU196,TRUE)=TRUE,,if(trends!FU196=0,,trends!FU$23)),),
IF(sel!$C$89=TRUE,IF(IFERROR(trends!GA196,TRUE)=TRUE,,if(trends!GA196=0,,trends!GA$23)),),
IF(sel!$C$90=TRUE,IF(IFERROR(trends!GG196,TRUE)=TRUE,,if(trends!GG196=0,,trends!GG$23)),),
IF(sel!$C$91=TRUE,IF(IFERROR(trends!GM196,TRUE)=TRUE,,if(trends!GM196=0,,trends!GM$23)),),
IF(sel!$C$92=TRUE,IF(IFERROR(trends!GS196,TRUE)=TRUE,,if(trends!GS196=0,,trends!GS$23)),),
IF(sel!$C$93=TRUE,IF(IFERROR(trends!GY196,TRUE)=TRUE,,if(trends!GY196=0,,trends!GY$23)),),
IF(sel!$C$94=TRUE,IF(IFERROR(trends!HE196,TRUE)=TRUE,,if(trends!HE196=0,,trends!HE$23)),),
IF(sel!$C$95=TRUE,IF(IFERROR(trends!HK196,TRUE)=TRUE,,if(trends!HK196=0,,trends!HK$23)),),
IF(sel!$C$96=TRUE,IF(IFERROR(trends!HQ196,TRUE)=TRUE,,if(trends!HQ196=0,,trends!HQ$23)),),
IF(sel!$C$97=TRUE,IF(IFERROR(trends!HW196,TRUE)=TRUE,,if(trends!HW196=0,,trends!HW$23)),),
IF(sel!$C$98=TRUE,IF(IFERROR(trends!IC196,TRUE)=TRUE,,if(trends!IC196=0,,trends!IC$23)),),
IF(sel!$C$99=TRUE,IF(IFERROR(trends!II196,TRUE)=TRUE,,if(trends!II196=0,,trends!II$23)))
)


Thanks for any help!

[tlafferty]

hmmm...a sample workbook might be helpful

[rylo]

Hi

How about selecting 2 rows that are "helper" rows, putting the result of each part of the formula in the same column as the data, then perform a calculation based on the totals of the rows.

As an example
I197: =IF(sel!$C$60=TRUE,(IF(IFERROR(trends!I196,TRUE)=TRUE,,if(trends!I196=0,,(trends!I196*trends!I$23) )))))
I198: =IF(sel!$C$60=TRUE,IF(IFERROR(trends!I196,TRUE)=TRUE,,if(trends!I196=0,,trends!I$23)),)

Then you could sum row 197 and divide it by the sum of row 198.

rylo