Excel functions, help with wildcards
This is a discussion about Excel functions, help with wildcards in the Windows Software category; 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 in our website and join the conversation
This subject has been archived. New comments and votes cannot be submitted.
Apr 22
Apr 23
0
2 minutes
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.
OP
Yeah, it does look like CUViper is right. I'll be doing more work on it tonight.
Thanks a ton,
-bZj
Thanks a ton,
-bZj
