PDA

View Full Version : User Defined Functions



paddysheeran
04-30-2012, 03:00 AM
Hi All,

I have a workbook with a number of Sumproduct and SUM Array forumulas. My Workbook is increasing in size and I need to keep this under control to avoid any problems with emailing it out.

My question is can I create a number of User Defined functions for the following formulas to reduce the szie of the file?

{=SUM((Incidents[Service]="WAN")*
(Incidents[SEVERITY]="P1")*
(Incidents[sla_attributable]="Yes")*
(Incidents[Site Class]="DC")*
(Incidents[TOTAL_OUTAGE_TIME__SECS_]))}

and

=SUMPRODUCT(
--(Incidents[SEVERITY]="P1"),
--(Incidents[sla_attributable]="Yes"),
--(Incidents[Site Class]="DC"),
--(Incidents[Service]="WAN"))

and

=IF(ISERROR(VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0)),"Check Matrix",VLOOKUP(LEFT(Y2,10),INDIRECT("'"&AC2&"'!A:J"),7,0))

thanks.

Paddy.

Bob Phillips
04-30-2012, 03:26 AM
I can see how such formulae might impact the efficiency of the workbook, but size? Can't see that is relevant.

BTW, if you have Excel 2007 or 2010, you can use

=IFERROR(VLOOKUP(...),"Check Matrix")

rather than

=IF(ISERROR(VLOOKUP(...),"Check Matrix",VLOOKUP(...))

paddysheeran
05-01-2012, 08:22 AM
Sorry I meant calculating time rather than file size.