Excel functions, help with wildcards
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 Fp = $12. 95 OR Op = $6.
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
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
Participate on our website and join the conversation
This topic is archived. New comments cannot be posted and votes cannot be cast.
Responses to this topic
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....
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....
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.
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.
