SOLVED

# If conditional multiplication

Copper Contributor

# 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

11 Replies

# Re: If conditional multiplication

Does the "£" show along with the value in the formula bar or is it added through cell formatting?

It’s formatting

# Re: If conditional multiplication

I;d use a separate column for the currency. You can then use a simple INDEX/MATCH formula instead of making Excel jump through hoops to determine the number format.

See the attached demo.

best response confirmed by Tarig Elsawi (Copper Contributor)
Solution

# Re: If conditional multiplication

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

# Re: If conditional multiplication

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?

# Re: If conditional multiplication

I guess @Hans Vogelaar has full functionality. Peter, I've seen you comment on MTC news, however, I'd suggest to involve @cuong .

# Re: If conditional multiplication

It hasn't happened to me, but as MVP I (like @Sergei Baklan) probably have a higher permissions level than you.

# Re: If conditional multiplication

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.

# Re: If conditional multiplication

It looks like it has to do with the new community ranks such as Silver Contributor - see What’s New in the MTC? September 2023 – Community Ranks

I hope that the powers that be will correct this soon.

# Re: If conditional multiplication

I understand this was a known issue that's been fixed. Please let us know otherwise.

# Re: If conditional multiplication

Yes. All is good now, thanks.

1 best response

Accepted Solutions
best response confirmed by Tarig Elsawi (Copper Contributor)
Solution

# Re: If conditional multiplication

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