Multiple IF OR functions (Nested)

Hi All,

In the attached file i am trying to convert USD to different currencies based on Pricing Category. For Example, if the pricing Category is Parts i want to convert to GBP. to do this i need to multiple USD*.524*1.28. If the pricing category is System, i want to convert to GBP and need to multiply USD*.694*1.85. I would assume i need an IF OR function, however, my experience with functions is somewhat limited. I had started the formula using =IF(OR(C2="PARTS",C2="PART KITS"),I2*0.524*1.28),IF(OR(C2="SYSTEMS",C2="SYSTEM KIT"),I2*0.694*1.85)

Any help is appeciated!

see attached.
The formula is fixed now.
modytrane

Do you have any recommendations if i have approximately 50 pricing categories? I know excel 2003 limits the number of arguments in a formula.

Look at the attached file.
That's a better way to do it.
Create your conversion table [as big as it needs to be].
Give it a name.
Reference the table in your formula.
That way if your factors change, you don't have to modify your formulas.
Look at the formulas under GBP.
If you need help, let me know.
modytrane

One more thing.
Notice the pull down menu under the pricing catagory.
That list is from the conversion table [left most column].
That way you can limit the user from mis-typing the catagory.
It maintains uniformity in the way the catagories are listed.
modytrane

Hi modytrane,

This formula worked great. One last component i would like to add is a rounding function. the rounding function should look something like =Round(L5/LOOKUP(J10,{0,100,500,1000;0.05,1,5,10{),0)*LOOKUP(L5,{0,100,500,1000;0.05,1,5,10})

This is saying if the number in L5 is between 0 and 100, round to the nearest .05, if the number in L5 is between 100.01 and 500 round to the nearest 5, etc.

I am not able to figure out how to incorporate this into the function you recommended above.

Thanks again for all of your help!

see the attached file.
MROUND function has been implemented in column L.
It's written to round to nearest 0.05 if the result is <= 100 and to nearest 5 if above 100. You mentioned 100.01 to 500, but didn't specify what to do if its over 500, so I just made it >100.
Hope this helps.
modytane

So if the rounding rules were:

0-100 round to nearest .05
100.01-500 round to nearest 1
500.01-1000 round to nearest 5
1000.01-999999 round to nearest 10

i understand your formula, but if i want to say if the value is between 500.01 and 1000 round to the nearest 5, but if it is between 1000.01 and 999999 round to the nearest 10?

Ok, I've changed the formula to take care of all of your conditions.
Hope this works for you.
modytrane