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.

Windows Software 5498 This topic was started by , . Last reply by ,


data/avatar/default/avatar13.webp

651 Posts
Location -
Joined 2000-07-31
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

Participate in our website and join the conversation

You already have an account on our website? To log in, use the link provided below.
Login
Create a new user account. Registration is free and takes only a few seconds.
Register
This subject has been archived. New comments and votes cannot be submitted.
Apr 22
Created
Apr 23
Last Response
0
Likes
2 minutes
Read Time
User User User
Users

Responses to this topic


data/avatar/default/avatar27.webp

1117 Posts
Location -
Joined 2000-01-23
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....

data/avatar/default/avatar17.webp

757 Posts
Location -
Joined 2000-10-14
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.

data/avatar/default/avatar13.webp

651 Posts
Location -
Joined 2000-07-31
OP
Yeah, it does look like CUViper is right. I'll be doing more work on it tonight.
 
Thanks a ton,
-bZj