Help with formula

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....
 
Code:
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)
 
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..
 
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.
 
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!
 
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.
 

Attachments

  • ExcelGuru 8313 Projects Summarised Over Exchange Rates and Period.xlsx
    24.5 KB · Views: 6
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!
 
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.
 
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!
 
You said the formula was too slow, so maybe VBA as Ali suggested.
 
Putting the exchange rates on a different worksheet will make no difference to the efficiency of the formula.
 
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.
 

Attachments

  • ExcelGuru8313Formula Query 1.xlsm
    44.5 KB · Views: 11
Last edited:
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 !!
 
Back
Top