A formula that's different on every row

[Maury Markowitz]

Bear with me here...

I have a sheet that is several thousand lines long with all the
inventory held in a particular bank account. Several of the columns
are formulas that contain an FX rate, and which rate to use depends on
the currency on the current line compared to the account.

If the currency is the same as the bank account's, I can just replace
the rate with "1". If it's a CAD holding in a USD account, then I want
to replace it with "0.97". If it's USD holding in a CAD account, I
want to use "1.03".

I've found that Excel seems to be much faster if you apply a single
formula to a range (column-by-column in this case) than looping over
the rows and putting a formula in each one one-by-one (perhaps this is
not true?).

So what I thought is that I should put in a placeholder of some sort,
something in the formula that I can just search and replace. Like
this...

"=(E5*F5*BE5)*FXRATE"

But it can't just say "FXRATE", it has to say something like
"FXRATEUSD" so I know what number to replace it with. For the life of
me I can't figure out how to write that formula. It's just a string
concat, right?

Any advice? Or should I just punt and roll over the rows in a loop?

Maury

 

[Rick Rothstein \(MVP - VB\)]

Can you give us a little more detail about the structure of your worksheet?
For example, where is the currency for the bank account and for the holdings
kept and how are they indicated? Where is the FX rate stored at and what
does its structure look like? We are probably talking about incorporating a
lookup function of some kind for what you are calling FXRATE, but without
more detail regarding the structure of your worksheet, I'm not sure anyone
will be able to give you a more detailed response.

Rick

 

[Tim Zych]

From how you describe this I would either:

Add a new column with the FX Rate to use, then the formula multiplies the
new column. Then all of the logic can go into the FX Rate To Use formula and
not in the key metric.

Extend the key metric to include all of the possible FX Rates. Make the
formula more sophisticated so that you can fill it down for new rows and it
will work without find/replace.