If conditional multiplication

[Tarig Elsawi]

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

[#2]

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

[#3]

It’s formatting

[#4]

@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.

[#5 · Accepted 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

[#6]

@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?

[#7]

@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 .

[#8]

@Peter Bartholomew 

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

[#9]

@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.

[#10]

@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.

[#11]

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

[#12]

@cuong 

Yes. All is good now, thanks.