Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 34

Thread: Help with formula

  1. #21
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription


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

    Thanks for letting me know.
    Ali
    Enthusiastic self-taught user of MS Excel!

  2. #22
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    Sorry I've just come across an issue. I need to delete some rows between Rows 19 to 63 (as I don't require all currencies for some of the activities) but when I do it the formulas in rows 5 to 13 break. ie they turn to #N/A. Do you know how I can fix this? Thanks again....

  3. #23
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,604
    Articles
    0
    Excel Version
    O365
    I have no idea if that is the best or even a good formula, but this change should overcome your problem

    =IFERROR(INDEX(D$19:D$58,MATCH(1,($B$19:$B$58=$B5)*($C$19:$C$58=$C5),0)),0)+
    IFERROR(INDEX(D$19:D$58,MATCH(1,($B$19:$B$58=$B5)*($C$19:$C$58="AUD"),0))*INDEX(D$62:D$69,MATCH("AUD",$C$62:$C$69,0)),0)+
    IFERROR(INDEX(D$19:D$58,MATCH(1,($B$19:$B$58=$B5)*($C$19:$C$58="NOK"),0))*INDEX(D$62:D$69,MATCH("NOK",$C$62:$C$69,0)),0)+
    IFERROR(INDEX(D$19:D$58,MATCH(1,($B$19:$B$58=$B5)*($C$19:$C$58="EUR"),0))*INDEX(D$62:D$69,MATCH("EUR",$C$62:$C$69,0)),0)+
    IFERROR(INDEX(D$19:D$58,MATCH(1,($B$19:$B$58=$B5)*($C$19:$C$58="SGD"),0))*INDEX(D$62:D$69,MATCH("SGD",$C$62:$C$69,0)),0)

  4. #24
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    OK I'll try this, thanks. My only concern is I have to drag this across 16 columns and the file is large - I have found that array formula's can slow files down. I'll give it a go tho and let you know. Thanks again for all your help..

  5. #25
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,205
    Articles
    0
    Excel Version
    Office 365 Subscription
    The fact that your real dataset is large perhaps ought to have been mentioned at the outset. If the array formula proves slow, you may need to look at a VBA solution.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #26
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    Sorry yes I should have mentioned this. I don't have experience setting up macros but good time to learn! I was thinking if I put the rates in to another worksheet this may help simplify the formulas? Unfort I have to put it aside now for month end reporting. I still have a couple of weeks to finalise it so I will try a few things and come back to you with any further queries in about 1 week's time if that's ok? Thanks again for your time and help, very appreciated!

  7. #27
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,604
    Articles
    0
    Excel Version
    O365
    Here is a solution without VBA and without formulae, but using Power Query which should be fast.

    I have deleted your results table as the PQ creates its own.

    I have added two rows for USD to the exchange rates, so as to keep the format/structure, but to ensure the PQ merge finds a match.

    I added a few values to other periods just to show it works correctly.

  8. #28
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    Sorry Bob I've been off work sick, back today. Thanks very much for that but I need to be able to replicate this for the real data (and understand it) so I will get online and try to learn power queries as I've never used this feature! I can do pivot tables - is this similar? I'll look online over the weekend anyhow.. thanks again!

  9. #29
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,604
    Articles
    0
    Excel Version
    O365
    No, Power Query is not like pivots. It is often used to create a dataset that is more pivot friendly than the unstructured or badly structured data that you often have to start with, but that is all. What it is is an in-memory ETL (Extract, Transform, and Load), so it takes data and manipulates it before loading the datamodel, which is what the pivot will use. It is very, very powerful.

  10. #30
    Acolyte Ajwilltravel's Avatar
    Join Date
    May 2017
    Posts
    22
    Articles
    0
    Excel Version
    MS Office Prof Plus 2013
    OK thanks - I'll try to get head around it. In case I don't get up to speed with this in time to get this report out, I don't suppose you could suggest an alternative formula, if I were to put the fx rates in a different worksheet - do you think that might make a difference to the (length of) the formula that Ali suggested? If not I'll just use Ali's for now. Thanks again!

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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