Consulting

Results 1 to 3 of 3

Thread: User Defined Functions

  1. #1

    User Defined Functions

    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?

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

    and

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

    and

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

    thanks.

    Paddy.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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(...))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Sorry I meant calculating time rather than file size.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •