Calculate the Total Amount based on Currency Selected from Drop Down List

Denny V

=IF([Currency]22 = "AED", [Amount]22, IF([Currency]23 = "USD", [Currency]23 * [Rate]23, [Amount]24 * [Rate]24))

Kelly Moore

Hey Denny

Did you build the Sheet Summary fields?

Kelly Moore

Hey @Denny V

One approach is to place the rates in your summary field (right hand ribbon of your page). This will allow you to reference the rates and keep your formula formatted as a column formula. The Summary fields provide an easy way to store this type of referenced data within the sheet so that you don't have to hard-code the rate directly into the formula. If the list of conversion factors grow, we would consider a separate sheet that we could use as a lookup table.

You will create a field for each rate in the Sheet summary section. You can call it whatever you like but I'll name them so you are clear on the formula.,

The formula below is what goes in your TOTAL column

=IF(Currency@row="AED", [AED Rate]#*Currency@row, IF(Currency@row="USD", [USD Rate]#*Currency@row, IF(Currency@row="EUR", [EUR Rate]#*Currency@row)))

Would this work for you?

Kelly

Denny V

Thanks, Kelly for the response. however, it is showing INVALID OPERATIONS

Kelly Moore

Hey Denny

Did you build the Sheet Summary fields?

Denny V

Yes made the following ,

Kelly Moore

Just checking, the formula is working now, or do we still need to tweak it?

Denny V

Dear Kelly ,

The Formulae worked . Thank for your Help