Optimizing LET Function with Multiple Variables

Hello,

I?m hoping there is a more efficient (defined as less CPU intensive, fewer lines, easier to read) method of rewriting the code below. Line breaks added for readability in this forum.

Please Login or Register  to view this content.

The purpose of this function is to:

1. Add up all revenue in the tblTimesheets table where the Billing Currency is CAD - and
2. The employee is still active (transpose to tblEmployee[End Date]) ? then
3. Multiply each month's SUM total by the end of month CAD to USD Exchange Rate (the ?K? column) - then
4. Add a months together (represents the total revenue converted to USD)

Note: the reason I don?t have a single CAD to USD exchange rate cell is exchange rates vary wildly throughout the year, meaning the difference between the exchange rate calculation in February and December could be millions of dollars.

I just added the first 3 months to validate that it works, however I feel than extending the function out to 12 months (e.g., 12 separate LET variables) will slow down the calculations in the overall spreadsheet.

Is there a more elegant or efficient way of doing this without creating a bunch of helper tables or VBA/Macros?