Retail Price rounding help needed

[Graham Clarke]

I have a formula that I found here in earlier searches, that works fairly
well, but has a couple of annomolies I'd like help with.

I have a cloumn of costs (A) , that I want to take a certain margin at. Say
40% margin. I just throw another column beside (B) it and get it to take -
Acell/.60
This gives me the raw retail at my 40% margin. The original problem was
this puts retails at 3.47, 6.24, etc. I wanted a formula to round up to the
nearest .09 cents up. This way I maintain my margin and price it at a proper
retail price.
The formula I got from this group was:

INT((10*A3)+1)/10-0.01 ---->A3 being the cell I want to round up

There are 3 price points however that I don't want rounded up to.
I don't want any price to end in .09, .59, and .89

Is there some IF/Then statement I can use or some additional formula that I
can add that will look for these 3 price points, and round another dime up.
So, if my price was 1.09, 2.09, 3.09, etc, these would round up to 1.19,
2.19, 3.19.
Same with the other 2 price points:
1.59, 2.59, 3.59 would round up to 1.69, 2.69, 3.69 etc.

Any help would be great. If you need further info, I will supply it.

It's almost as if I need a wildcard in my formula - "If A1 = x.09, then add
$0.10
Unfortunately x, nor any other wildcards have worked for me.

Thanks all

Graham

[JE McGimpsey]

I'm sure there's a more elegant solution, but here's one way:

=CEILING(A3/0.6, 0.1) - CHOOSE(MOD(CEILING(A3/0.06,1),10)+1, 0.01,
-0.09, 0.01, 0.01, 0.01, 0.01, -0.09, 0.01, 0.01, -0.09)


In article <[email protected]>,
Graham Clarke <[email protected]> wrote:

> I have a formula that I found here in earlier searches, that works fairly
> well, but has a couple of annomolies I'd like help with.
>
> I have a cloumn of costs (A) , that I want to take a certain margin at. Say
> 40% margin. I just throw another column beside (B) it and get it to take -
> Acell/.60
> This gives me the raw retail at my 40% margin. The original problem was
> this puts retails at 3.47, 6.24, etc. I wanted a formula to round up to the
> nearest .09 cents up. This way I maintain my margin and price it at a proper
> retail price.
> The formula I got from this group was:
>
> INT((10*A3)+1)/10-0.01 ---->A3 being the cell I want to round up
>
> There are 3 price points however that I don't want rounded up to.
> I don't want any price to end in .09, .59, and .89
>
> Is there some IF/Then statement I can use or some additional formula that I
> can add that will look for these 3 price points, and round another dime up.
> So, if my price was 1.09, 2.09, 3.09, etc, these would round up to 1.19,
> 2.19, 3.19.
> Same with the other 2 price points:
> 1.59, 2.59, 3.59 would round up to 1.69, 2.69, 3.69 etc.
>
> Any help would be great. If you need further info, I will supply it.
>
> It's almost as if I need a wildcard in my formula - "If A1 = x.09, then add
> $0.10
> Unfortunately x, nor any other wildcards have worked for me.
>
> Thanks all
>
> Graham