Forum Discussion
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 the exchange table and multiply by 150.
thank you
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
11 Replies
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
Hans, the board has suddenly decided that I should not be allowed to post pictures. Has any such thing happened to you or have I been singled out for demotion?
- cuong
Microsoft
I understand this was a known issue that's been fixed. Please let us know otherwise.
Does the "£" show along with the value in the formula bar or is it added through cell formatting?
- 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.
