Want to multiply cells if value is "USD"

**Hitman Hotrods** · 01-28-2010, 05:05 PM

I am pretty green using excel. I have a price list made with 7 colums. One of the columns specifies wheather the part is canadian CDN or United States US. The next column is our cost in US or CDN dollars. I have also created a chart that has the current exchange rate posted. How do I multiply our cost by the exchange rate if the value in the one column is US.

Thanks
Scott

**NBVC** · 01-28-2010, 05:10 PM

=X1*IF(Y1="US",$Z$1,1)

where X1 is the value, Y1 is the currency and Z1 is the exchange rate..

So if Y1 has US, then it multiplies the value that is in X1 by the exchange rate in Z1...

Adjust cell references as necessary.. Note if you copy formula down it will always refer to Z1 for the exchange rate.

**Hitman Hotrods** · 01-28-2010, 06:04 PM

the cell where the formula was added now reads #Value!

**daddylonglegs** · 01-28-2010, 06:08 PM

can you post the formula you actually used if it's different from NBVC's suggestion?

**teylyn** · 01-28-2010, 06:22 PM

is X1 a number?

**Hitman Hotrods** · 01-28-2010, 07:29 PM

=F1*IF(D2="us",$I$2,1)
I don't understand what the last 1 is refering to.

**daddylonglegs** · 01-28-2010, 07:45 PM

The IF statement returns either the exchange rate in $I$2 or 1 and then that number is multiplied by F1, so if currency is US you get F1 multiplied by the exchange rate, if it's not you get F1 multiplied by 1 (which, obviously, is the same as just F1, you could write it like this too

=IF(D2="us",$I$2*F1,F1)

perhaps a little more transparent

either way if you get #VALUE! error that probably means that either F1 or $I$2 isn't a number

test that by using these formulas

=ISNUMBER(F1+0)

=ISNUMBER($I$2+0)

they should both be true

**Hitman Hotrods** · 01-28-2010, 07:53 PM

I got it to work. Yes I had an incorrect value for one of the cells.
where could I go to find a break down on the symbols that are used for different comands.

**Hitman Hotrods** · 01-28-2010, 08:11 PM

next question with a similar function. I use 6 different magins based on different levels of purchase price. so with the =E3*IF(D3="US",$I$2,1) e3 being the value I want to mark up, how do I write this.
This program is amazing and I beleive the possibilities are endless.

**teylyn** · 01-28-2010, 08:22 PM

give us your 6 different margin values and the corresponding purchase price thresholds, then we can show you how to put that into a LOOKUP and combine with your formula, or how to set up a table where you can lookup the value you want to mark up based on the purchase price.