Search code examples
excelexcel-formulaarray-formulas

Excel Array formula, use info from one IF result as criteria in another IF?

Context

I'm building a financial dashboard, but I'm having troubles to get a formula that fit my client's need.

I'm consolidating amount in different currencies, but for a special indicator,
I need to build a YTD with the Exchange Rate of the last month.

Something like :

(Amount_$_Jan + Amount_$_Feb)*ExRate_$_Feb + (Amount_£_Jan + Amount_£_Feb)*ExRate_£_Feb

OR

(Amount_$_Jan + Amount_$_Feb + Amount_$_Mar)*ExRate_$_Mar + (Amount_£_Jan + Amount_£_Feb + Amount_£_Mar)*ExRate_£_Mar

My issue

In the data, I have multiple currencies and they'll be more to come, so I cannot list the currencies.

I'm trying to :

  • get the value of the currency of each line that matches the criteria of the first IF
  • to use it in my second IF to find the exchange range for that currency
    for the month I'm calculating for,
    with: Named_Rg[Currency]=Named_Rg[Currency]

which is obviously always true, but it is the only syntax I've tried that I could validate...

I've tried :

  • Named_Rg[Currency]=[@[Currency]]
  • Named_Rg[Currency]=[Currency]

But both are giving errors (I'm using that formula outside of the table Named_Rg)

I know I can write a function in VBA, but I'd prefer to keep an xlsx.

My formula

I've removed some tests, like testing the year, which are not pertinent for the question.

I'm using it on a another sheet that the one where the table Named_Rg is :

{=SUM(IF(Named_Rg[Month]<=MONTH(X$5);Named_Rg[Amount]*IF(AND(Named_Rg[Month]=MONTH(X$5);Named_Rg[Currency]=Named_Rg[Currency]);Named_Rg[Chg to €];0);0))}

How can I refer to the Row/Currency found with the first IF in the second one?

Sample Data

That is just a sample, I'll have multiples rows per month and currency.

Year    Month   Currency    Chg to €    Amount
2017    1       EUR         1           20
2017    1       USD         0.6         30
2017    1       LST         2           40
2017    2       EUR         1           200
2017    2       USD         0.7         300
2017    2       LST         2.2         400
2017    3       EUR         1           2000
2017    3       USD         0.8         3000
2017    3       LST         2.4         4000

CSV format :

Year;Month;Currency;Chg to €;Amount
2017;1;EUR;1;20
2017;1;USD;0.6;30
2017;1;LST;2;40
2017;2;EUR;1;200
2017;2;USD;0.7;300
2017;2;LST;2.2;400
2017;3;EUR;1;2000
2017;3;USD;0.8;3000
2017;3;LST;2.4;4000

Expected results :

YTD last chg (Jan) : 118 = 20*1+30*0.6+40*2
YTD last chg (Feb) : 1419 = (20+200)*1+(30+300)*0.7+(40+400)*2.2
YTD last chg (Mar) : 15540 = (20+200+2000)*1+(30+300+3000)*0.8+(40+400+4000)*2.4

Solution

  • Array formula do not like the AND() or OR() operators. They need to be substituted with * or + respectively.

    So your:

    AND(Named_Rg[Month]=MONTH(X$5);Named_Rg[Currency]=Named_Rg[Currency])

    Should be:

    (Named_Rg[Month]=MONTH(X$5))*(Named_Rg[Currency]=Named_Rg[@Currency])

    So the formula would be:

    =SUM(IF(Named_Rg[Month]<=MONTH(X$5);Named_Rg[Amount]*IF((Named_Rg[Month]=MONTH(X$5))*(Named_Rg[Currency]=Named_Rg[@Currency]);Named_Rg[Chg to €])))

    Remember that this is an array formula and needs to be confirmed with Ctrl-Shift-Enter

    enter image description here

    But I think you want this formula instead to get the desired output:

    =SUMPRODUCT(SUMIFS(Named_Rg[Amount],Named_Rg[Month],"<=" & MONTH(X5),Named_Rg[Currency],Named_Rg[Currency])*(Named_Rg[Month]=MONTH(X5))*(Named_Rg[Chg to €]))

    Change the , to your ; for your local settings.

    ![enter image description here