Calculating multiple currencies against Different Rate Types, into USD.

Al Calabrese

I have a set of columns, "Rate" "Rate Type" and "Adjstd Rate (USD)". Rate is a numerical value, formatted to specific currency. Rate Type indicates whether the rate is a Daily Rate (Made up of 8 hours), an Hourly Rate. The following are some examples outlining what I am looking to achieve with (maybe w/ sumifs?)

EG: £800.00, Day Rate is $134.00 @ hr. This is achieved by simply multiplying the rate by 1.3(standardized conversion from GBP to USD) then dividing by 8.

EG2: €800.00 Day Rate * 1.1 /8 = $110.00

EG3: £32.00 Hourly $41.60

Is there a way for a formula to recognize the currency, and apply conversion (1.3 and 1.1 respectively) and then depending on the Rate Type, divide by 8 or not?

Can it be a column formula?

Christina09

@Al Calabrese

Can you add a helper column? Add a dropdown column for currency, select which currency, and depending on that, create a IF formula.

Will this work?

Christina09

@Al Calabrese

Can you add a helper column? Add a dropdown column for currency, select which currency, and depending on that, create a IF formula.

Will this work?

Al Calabrese

That If statement is a thing of beauty. Thank you.