What formula to use

[Akankssha Gupta]

Hi,

PFA a Sample file.

I need to enter the Net Amount in 'Data' Sheet in USD. This is a sample file, and I have several thousands of records in my actual data of different currencies.
The catch is that I have to reference the date and use that month's currency exchange value. All my dates are within 2013, so I've included a currency exchange sheet in the sample file with currency rates of each of the months.

I need a very short formula that doesn't take processing time, because my data set is very large, and also I need to use further formulae in there.

Any help would be much appreciated.

Thanks and Regards
Akankssha

 

[Mike H..]

Hi,

PFA a Sample file.

I need to enter the Net Amount in 'Data' Sheet in USD. This is a sample file, and I have several thousands of records in my actual data of different currencies.
The catch is that I have to reference the date and use that month's currency exchange value. All my dates are within 2013, so I've included a currency exchange sheet in the sample file with currency rates of each of the months.

I need a very short formula that doesn't take processing time, because my data set is very large, and also I need to use further formulae in there.

Any help would be much appreciated.

Thanks and Regards
Akankssha

Hi,

Have a look at this file.

 

[Asheesh]

Hi,

PFA a Sample file.

I need to enter the Net Amount in 'Data' Sheet in USD. This is a sample file, and I have several thousands of records in my actual data of different currencies.
The catch is that I have to reference the date and use that month's currency exchange value. All my dates are within 2013, so I've included a currency exchange sheet in the sample file with currency rates of each of the months.

I need a very short formula that doesn't take processing time, because my data set is very large, and also I need to use further formulae in there.

Any help would be much appreciated.

Thanks and Regards
Akankssha

Put this formula in D2 of data tab

=IFERROR(INDEX(Table1,MATCH(Table2[[#This Row],[Currency]],Table1[Currency code ▲▼],0),MATCH(TEXT(Table2[[#This Row],[Posting Date]],"mmmm"),Table1[[#Headers],[January]:[December]],0)+2)*Table2[[#This Row],[Net Amount]],"-")

 

[Akankssha Gupta]

Hi guys,

Thanks so much for looking into this. However, I'm finding it difficult to replicate the formula based on the table formatting. I've once again uploaded the file, with the data (now unformatted)
Could you give me the formula based on this file?

 

[Akankssha Gupta]

here's the file

 

[Mike H..]

Hi guys,

Thanks so much for looking into this. However, I'm finding it difficult to replicate the formula based on the table formatting. I've once again uploaded the file, with the data (now unformatted)
Could you give me the formula based on this file?

I only see the original file.

Have a look at this one where I've converted the tables to ranges. The formula should be easier for you to convert.

 

[Asheesh]

Hi guys,

Thanks so much for looking into this. However, I'm finding it difficult to replicate the formula based on the table formatting. I've once again uploaded the file, with the data (now unformatted)
Could you give me the formula based on this file?

here's the file

I am unable to attach the file...however, if your data is not in table format then the formula is...

=IFERROR($B2*INDEX('Currency Exchange'!$A$2:$N$168,MATCH(Data!$C2,'Currency Exchange'!$A$2:$A$168,0),MATCH(TEXT(Data!$A2,"mmmm"),'Currency Exchange'!$A$1:$N$1,0)),"-")

Put this in D2 & Drag it down...