Posts by zdjbel

Thank you so much Carim, didn't realize it was this simple, I was obviously on the wrong path trying to do it with ISBLANK.

Hello,

I have a table where I want a formula in cell C2 to divide the amount in A2 (e.g. by 4) if populated, or if blank to divide the amount in cell B2 instead.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 23"] [/TD]
[TD="width: 58"]A[/TD]
[TD="width: 49"]B[/TD]
[TD="width: 75"]C[/TD]

[/tr]

[tr]

[td]

1

[/td]

[td]

CAT 1

[/td]

[td]

CAT2

[/td]

[td]

1Q

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td][/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

125

[/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

Anybody have any ideas how I can accomplish this?

Thanks in advance,

zdjbel

That did the trick. Thank you for the solution and the lesson, now I know how to nest multiple IF statements in a formula.

All the best! :thanx:

Z

This thread is SOLVED, but I can't find how to mark it as such.

Hi NBVC,

Your formula works to bring in the corresponding rate of exchange (ROE) just fine and that completes the first step of what I want to accomplish. The next step is to calculate the USD equivalent of the amount: divide Total by ROE if the currency is RSD, multiply Total by the ROE is the currency is EUR or just copy Total if the currency us USD. I'm certain that this would make the formula way too complex and slow to execute over my 20,000+ records so having a separate column, i.e. formula to do this portion of the calculations is absolutely fine.

Thank you very much in advance.

Z

Hello Alan,

Thanks very much for the formula, unfortunately it gives errors for non-USD currencies. This is what I got, not sure how/where to edit the formula to get the desired result...

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.JPG","data-attachmentid":1210344}[/ATTACH]

Many thanks!

Z

Hello,

I have a workbook where Worksheet1 has data with amounts paid in 3 different currencies, and I need a column to calculate the equivalent of the expense in a single currency. The workbook also contains Worksheet2 with rates of exchange which change on a daily basis. How can I find the corresponding date/currency/rate of exchange in Worksheet2 and calculate the USD equivalent if the amount paid was not in USD, and just copy the USD amount to the cell if it was paid in USD? The solution must be a formula, not a macro or VBA as they're not allowed where I work.

Attached is a spreadsheet with sample data.

Thank you very much.
ZdjBel

Re: Identify currency format and store abbreviated currency name in adjacent cell

Hello snb,

Thank you, this is a far more elegant solution.

zdjbel

Re: Identify currency format and store abbreviated currency name in adjacent cell

I found a solution.

1. A custom function needs to be inserted into a standard module. The function and instructions are available at this link: http://www.pcreview.co.uk/foru…mber-format-t3780863.html
2. In the adjacent cell (B1 if the number for evaluation is in A1) call the function. That will convert the contents of A1 from currency or number to text format
3. Insert Windy58's formula =IF(LEFT(A2,1)="$","USD",IF(LEFT(A2,1)="E","EUR","RSD")) in the next adjacent cell, e.g. C1, to extract the currency abbreviation(s).

Re: Identify currency format and store abbreviated currency name in adjacent cell

Hi Windy,

That works if the first character of A2 is a text type character, but my issue is that the currency in the cell is determined through formatting (see screenshot), not as a text character. The users who input the data into the cell format it in accordance with their local currency. What I'm trying to achieve is to identify the format of cell A2, and then execute the rest of the fomula as you wrote it.

[ATTACH=CONFIG]60275[/ATTACH]
Thanks very much,

Zdjbel

Hello,

I have a spreadsheet with a column for total amounts which are randomly formatted as standard number or $ currency or Euro currency. I need to find a way to identify the currency used in the cell and then store the abbreviated currency name in the adjacent cell, e.g if the number in the Total column is $, then insert "USD" in the CURR column. The formula would need to evaluate and convert 3 possible formats: plain number = RSD, $ format = USD and E (Euro symbol) = EUR

[TABLE="class: grid, width: 500, align: left"]

[tr]

[td]

Total

[/td]

[td]

CURR

[/td]

[/tr]

[tr]

[td]

$500.00

[/td]

[td]

USD

[/td]

[/tr]

[tr]

[td]

E26.33

[/td]

[td]

EUR

[/td]

[/tr]

[tr]

[td]

255.22

[/td]

[td]

RSD

[/td]

[/tr]

[/TABLE]

Thanks very much,

Zdjbel