Forum Discussion
Solved
If conditional multiplication
Hi, i have a table containing exchange rate and I would like excel to multiply cells based on currency symbol on each cell. for example, if the cell is £150 then excel should lookup £ exchange from...
Depending upon the situation, one possible solution is to write a named Lambda function
ExtractCurrencyFormatλ = LAMBDA(ref, LET( rawFormat, GET.CELL(7, ref), TEXTBEFORE(TEXTAFTER(rawFormat, "["), "]") ) )The worksheet formula would be
= ExtractCurrencyFormatλ(currencyAmount) for a single value, or = MAP(currencyAmount, ExtractCurrencyFormatλ) for an array.Reference:
How to find the currency format in a formula | MrExcel Message Board
It’s formatting
You could use the CELL function to identify the cell format.
=CELL("format",A1)
If £150 is A1, for example, the above returns ",2"
if $150 is A1, the above would return "C2"
Why you might not want to use CELL:
- it's slow
- it doesn't spill
and this:
Note: If the info_type argument in the CELL function is "format" and you later apply a different format to the referenced cell, you must recalculate the worksheet (press F9) to update the results of the CELL function.