SOLVED

If conditional multiplication

Copper Contributor

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

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

It’s formatting

@Tarig Elsawi 

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

@Tarig Elsawi 

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 Vogelaar 

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?

@Peter Bartholomew 

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

@Peter Bartholomew 

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

@Tarig Elsawi 

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.

 

@Peter Bartholomew 

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.

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

@cuong 

Yes. All is good now, thanks.

1 best response

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

@Tarig Elsawi 

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

View solution in original post