Excel Help

[Eli]

So I have a column with different currencies in every cell:
A1: 5$.
A2: 10 NIS.
A3: 8$.
etc.

I want to sum the total of money in that column, in one of the currencies. So if I want a result in NIS and say that B1 is the exchange rate, I want to do: =5*B1+10+8*B1.

Problem is, there are too many cells to do this the stupid way.

Now, every cell is of the currency type, either USD or NIS. What I'm thinking about is this:

=SUMIF(A1:A1000,if USD,A1:A1000)+B1*SUMIF(A1:A1000,if NIS,A1:A1000)

But how do I phrase the "if USD"/"if NIS" condition in excel?

[DanS]

Don't know that it has those kinds of formatting conditions. Probably your best bet is to do it the stupid way. So your A column would be amount, B column would be currency, C would be exchange rate to shekels, D would be amount in shekels, etc.

Added bonus is that this is database import-friendly and doesn't lend itself to inadvertently getting figures losing currency context due to formatting changes (happens surprisingly frequently in my line of work). Anybody gives you grief on doing it the stupid way, just say that you're planning ahead.

Good luck.

[lord of the mark]

Re: Excel Help

Originally posted by Eli
So I have a column with different currencies in every cell:
A1: 5$.
A2: 10 NIS.
A3: 8$.
etc.

I want to sum the total of money in that column, in one of the currencies. So if I want a result in NIS and say that B1 is the exchange rate, I want to do: =5*B1+10+8*B1.

Problem is, there are too many cells to do this the stupid way.

Now, every cell is of the currency type, either USD or NIS. What I'm thinking about is this:

=SUMIF(A1:A1000,if USD,A1:A1000)+B1*SUMIF(A1:A1000,if NIS,A1:A1000)

But how do I phrase the "if USD"/"if NIS" condition in excel?

ive never used it, but Excel has a function called "dollar" which it says converts number to text, using currency format. If it gives the right currency, then youve got an alpha numeric including the currency descriptor, which you can then apply an alpha function, either directly doing an if statement, or sorting, or whatever.

edit, that doesnt seem to work as the function always inserts the text "dollars".

[Sirotnikov]

if you upload the excel I could probably solve it.

it is hard for me to think outside of excel context.

[lord of the mark]

the problem is finding ANYTHING in Excel lets you perform an operation on the format of the cell, since that is the only distinction here. Logical tests dont work, so a regular if statement wont. Sorts on numeric fields are on the value, not the format. So youd need a special function, and i dont know of any.

[lord of the mark]

try the function "Cell"

info type is "format"

should return a code that differs for different currencies. Then you can take it from there.

and have a "zissen pesach."

[Eli]

Stupid way it is, then.

Dan:

Good suggestion.

[Kuciwalker]

cell(info_type, reference) will return format information, but it won't return which currency, sorry. AFAICT the function doesn't exist.

[lord of the mark]

Originally posted by Eli
Stupid way it is, then.

Dan:

Good suggestion.

The stupid way is stupid if you have a lot of rows.

My way should work. Try it. Once you have the format code, you can do an if statement on the format code. then convert the results to text, by paste special values. Then compare to a code in a fixed cell location, using the function "exact" and you have a column of true and false for a given currency.

[lord of the mark]

Originally posted by Kuciwalker
cell(info_type, reference) will return format information, but it won't return which currency, sorry. AFAICT the function doesn't exist.

I just tried it, using some cells formatted as currency dollars, and some as currency euros. It returns different values, because Excel thinks those are different formats.

[Kuciwalker]

It didn't for me, using USD and GBP.

[lord of the mark]

Originally posted by Kuciwalker
It didn't for me, using USD and GBP.

I actually used USD (with a dollar sign) and French (Belgian - dont ask me why) with a euro sign. I guess if you only have three character alpha, rather than a currency sign, Excel thinks its the same format.

Which seems odd.

[lord of the mark]

since it seems to be currency format specific, I was going to try $ vs NIS, but my version of Excel seems to be the AntiZionist special, as it does not have NIS listed.

[Kuciwalker]

I noticed that too.

[Lord Avalon]

Originally posted by DanS
Don't know that it has those kinds of formatting conditions. Probably your best bet is to do it the stupid way. So your A column would be amount, B column would be currency, C would be exchange rate to shekels, D would be amount in shekels, etc.

Added bonus is that this is database import-friendly and doesn't lend itself to inadvertently getting figures losing currency context due to formatting changes (happens surprisingly frequently in my line of work). Anybody gives you grief on doing it the stupid way, just say that you're planning ahead.

Good luck.

You could skip the column for exchange rate and just have it in a cell at the top, or off to the side.