Excel functions, help with wildcards

[Down8 0]

OK, I'm working on a pricelist for a friend's company in Excel. The standard pricing method is Op*2=x, and Fp~x.95, where Op=Original price and Fp=Final price.

 

So if:

Op = $6.00

[6 * 2 = 12]

Fp = $12.95

OR

Op = $6.75

[6.75 * 2 = 13.50]

Fp = $13.95

 

Is this making sense?

 

Originally, I was using the formula: =(Op*2+0.95), but this gives me $14.45 for the second example above, instead of $13.95.

 

I've searched through the help files for wild cards, as I was thinking something along the lines of this:

 

=IF((2*Op)="_?_.50",(2*Op+0.45),(2*Op+0.95))

 

Where in "_?_.45", _?_ = Excel's text wildcard, which I can't seem to find - or even find if there is one.

 

So, the question is how would I make Excel check for what comes after the decimal, and add $0.45 vs. $0.95. Or is there some way to work around this, and subtract $0.50 if it goes to $_?_.45?

 

Thanks for any help,

-bZj

[CUViper 0]

If I understand what you're trying to do, it will be easier if you just round Op*2 down - the formula you should use is this: =INT(Op*2)+0.95

 

If that's not what you want, here's how to make your formula work: =IF((2*Op-INT(2*Op))=0.50,(2*Op+0.45),(2*Op+0.95))

 

However, this assumes that Op will be a multiple of .25 - eg, if Op was 6.40, then your formula gets 13.75, whereas the first formula I gave gives 12.95. At any rate, my formula will work for both examples you gave and in the more general case as well.

 

Let me know if that works for you....

[CrazyKillerMan 0]

I am kinda confused here of what exactly you want.

 

Quote:

Originally, I was using the formula: =(Op*2+0.95), but this gives me .45 for the second example above, instead of .95.

 

When you use the formula =x.95 , this will only work for whole numbers, excel will give you the incorret value by rounding up or down so the number is whole.

$14.45 is the correct answer...so this is where it gets confusing for me.

You could set up a sort of "menu" system in excel by throwing the value of x in some feild....then saying

FP=x.b1 ---- or something. This way you could change the value you want to add.

I thnk CUViper hit it on the nose as far as i understand.

[Down8 0]

Yeah, it does look like CUViper is right. I'll be doing more work on it tonight.

 

Thanks a ton,

-bZj