Multifield formula for exchange rate calculation by looking up year and country

[MRoyW]

Hi, I have some hefty data of the following nature that I would like to change currency:

Country	Year	Value1	Value2	Value3	Value4
X	FY17	100	300	250	400
X	FY18	100	300	250	400
Y	FY19	100	300	250	400
X	FY17	100	300	250	400
Z	FY18	100	300	250	400
Y	FY17	100	300	250	400
X	FY18	100	300	250	400

Now since I know the exchange rate of a particular country for a specific year, I would like a multifield formula that would look up the country and year, then divide each valued column by the specific exchange rate. The exchange rate can be input within the formula or from a table input. I am open to recommendations on the best way.

Can anyone figure out a way to do this? I am uncertain of how to achieve this since both string and double fields would be considered in the formula with only double fields bearing the calculation.

Or is there another way?
Thanks

[Jonathan-Sherman · Accepted solution]

Hi @MRoyW,

This is how I would approach it, I wouldn't use the multi field but instead transpose, perform the calculation and cross tab back. However the multi field approach would also work.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[MRoyW]

Thanks @Jonathan-Sherman, out of curiosity, how would the multifield formula work?

[Jonathan-Sherman]

Hi @MRoyW,

The multi field approach could look something like this:

If this solves your issue please mark the answer as correct, if not let me know! I've attached an updated workflow for you to download if needed.

Regards,

Jonathan

[MRoyW]

Hey @Jonathan-Sherman, I think you've attached the first workflow for the second method

[Jonathan-Sherman]

Hi @MRoyW,

I've kept both workflows in the file, they are one below another.

Regards,

Jonathan

[MRoyW]

@Jonathan-Sherman Oh, I hadn't realized that.

Thank you so much for your assistance.