Results 1 to 6 of 6

User Defined Function - Sumproduct

Threaded View

  1. #1
    Registered User
    Join Date
    02-24-2004
    Location
    Brisbane
    Posts
    29

    User Defined Function - Sumproduct

    Hi there

    I have a very large formula that works. At the moment the formula exists in a cell on a worksheet and I have written some code to copy the formula to other cells on the worksheet and then after calculating the formula the code turns the resulting output into a value. I have had to do this as the formula is so resource intensive that if the formula lived in each of the cells the model would run too slowly.

    I would now like to turn the formula into a UDF, so that I can protect it from the users, but I have no idea how to start... I'm not expecting a converted formula, just some hints on how to go about it ...

    The formula is:

    =IF(O$3="Opening Bal",O_Financials_Actual!O12,
    IF(ROW()<ROW(modCashFlowStartRow),
    (
    SUMPRODUCT((I_Budget_Worksheet!$E$13:$E$288=$E12)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))-
    SUMPRODUCT((I_Budget_Worksheet!$F$13:$F$288=$E12)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))
    *
    IF(O_Financials_Budget!$F12="Cr",-1,1))+
    IF(O_Financials_Budget!$G12="***",O_Financials_Budget!N12,0),
    SUMPRODUCT((I_Budget_Worksheet!$G$13:$G$288=$E12)*(I_Budget_Worksheet!$E$13:$E$288=Bank_Account_Identifier)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))-
    SUMPRODUCT((I_Budget_Worksheet!$G$13:$G$288=$E12)*(I_Budget_Worksheet!$F$13:$F$288=Bank_Account_Identifier)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))
    ))

    Thanks in advance
    Peter
    Last edited by PeterW; 05-22-2008 at 10:09 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1