Page 4 of 4 FirstFirst ... 2 3 4
Results 31 to 34 of 34

Thread: Help with formula

  1. #31
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    You said the formula was too slow, so maybe VBA as Ali suggested.

  2. #32
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,248
    Articles
    0
    Excel Version
    Office 365 Subscription
    Putting the exchange rates on a different worksheet will make no difference to the efficiency of the formula.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #33
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    What fun.

    While I'm sometimes very satisfied with a long formula that I've developed that fits the bill exactly and copes well with what's thrown at it, I too often find when coming back to it to tweak/maintain it that it's very hard work remembering the logic I used to develop it, and so very hard work to adjust it. With that in mind I came up with a different approach.
    In the attached is a green area of cells which is (nearly) the same size as your table at cell A18. In all this 10x45 grid is one single array formula entered in one go. The idea, hopefully, being that Excel calculates the formula only once internally to return the entire grid, instead of it having repeatedly to calculate a complex formula on a cell-by-cell basis. You tell me if it works any faster. On top of that, the array formula is relatively short:
    =D19:M63*(INDEX($D$67:$M$76,MATCH($C19:$C63,$C$67:$C$76,0)+$O$16,COLUMN(A1:J1)))
    (and I'm not even sure I need all those $ symbols).
    The length of the formula has been reduced by adding a couple of rows of USD/USD conversion factors (of 1) at the bottom of your table which begins at around cell B67, so that the formula doesn't need to make a special case of USD values.

    After that, in your results table at the top I used again a relatively short formula to consolidate the results:
    =SUMIF($B$19:$B$63,$B5,O$19:O$63)
    which can be copied down and across. It's a plain formula, NOT an array-formula.

    The thing I'm not clear about is when you want to use Budget exchange rates, and when Forecast exchange rates; at the moment I've set up a cell (O16) where you can enter a one or zero that will tell the formula which rates to use.

    That's it. Those two formulae should be a lot easier to adjust. And the green table can be hidden away elsewhere, say on another sheet.

    Independently from the results table (at cell B4) above, I also set up another table, derived from the green grid, which is just links to all the green cells, rearranged to create a flat-file-like database suitable as a data source for a pivot table, which I've also added (at cell G79). Both pivot and results table tally. Add/Change values in your original table and both the green cells and the pivot data source update immediately, 'though the pivot table will need to be refreshed. It may be an avenue you want to explore.
    (There are a couple of short macros to create this pivot source table that I've left in the attached.)

    Please note that this has been developed on an old machine with only Excel 2003 and Win XP as I'm away from home at the moment, so some more recent worksheet functions haven't been used, and the pivot table is a bit raw.
    Attached Files Attached Files
    Last edited by p45cal; 2017-09-09 at 11:05 PM.

  4. #34
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    Sorry for the very late reply - I had to put this down fro month end reporting but I will look at this tomorrow and revert back. Thanks so much for your help, very appreciated !!

Page 4 of 4 FirstFirst ... 2 3 4

Posting Permissions

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