Price increment formula

[jamesfish]

i need to round prices up to .29/.49/.69/.99 eg $4.49-$4.69 etc

when a price ends in
0-.28= .29
30-48= .49
50-68= .69
70-98= .99

If anyone can help with a formula to do this I would be very thankful

Regards James

[dilipandey]

Hi Jameshfish,

Welcome to the forum.
Do you want this rounding to happen till 98 .. or the list can be bigger than the data you shown ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[jamesfish]

Thanks for quick response
its for pricing so .98 is the highest.
ie $4.98 becomes $4.99

[Ace_XL]

Try..

=IF(A1=ROUNDUP(A1,1),A1+0.09,ROUNDUP(A1,1)-0.01)

[jamesfish]

thanks am not a great excel maven - how do I add the price to the formula
ie
4.95 in column c for example what do i need to add so it will work to show $4.99
Thanks

[Ace_XL]

You would need to use the formula in an adjacent cell. So for example if your prices are in A1, type the formula in B1 and so on.

[jamesfish]

Just tested a few and see how it worked great
BUT
my problem is I have 4 columns with 4 prices
Total Ticket Price A Price B Price C Price

each column is worked with a different multiplier (x4.5/x2.15 etc

will try to see if the formula will work
Thanks

[Cutter]

@ Ace
Your formula isn't giving the expected results as I understand them. For instance a price of $4.30 yields $4.39 but I think OP wants $4.49

Maybe
with the original value in cell C2:
=INT(C2)+LOOKUP(MOD(C2,1),{0,0.29,0.49,0.69},{0.29,0.49,0.69,0.99})

[jamesfish]

cannot quite get there but with your help am nearly there .How do i put in that the formula is
f2=your formula * 2.5

[Ace_XL]

@ Ace
Your formula isn't giving the expected results as I understand them. For instance a price of $4.30 yields $4.39 but I think OP wants $4.49

Oops! thought it was increments of 0.10. Mid-week fatigue maybe! Thanks for correcting Cutter!

[jamesfish]

cutter your formula will not work on my excel it just shows 0.29 what am i doing wrong

[Cutter]

Sorry, I just gave you the base formula to work with.

You'll get .29 if the source cell is 0 or blank so to test if the source cell is blank:
=IF(C2="","",INT(C2)+LOOKUP(MOD(C2,1),{0,0.29,0.49,0.69},{0.29,0.49,0.69,0.99}))

Mid-week fatigue maybe!

Hate to tell you Ace, it's only Monday!

---------- Post added at 04:35 PM ---------- Previous post was at 04:30 PM ----------

Just noticed post #9

Try
=IF(C2="","",ROUND((INT(C2)+LOOKUP(MOD(C2,1),{0,0.29,0.49,0.69},{0.29,0.49,0.69,0.99}))*2.5,2))

[jamesfish]

thanks that is great but where do i put the multiplier so it will multiply a column by 2.5/4.5 etc Thanks so much

ps how do you get it so that the formula is in a spot but does not show 0.00 etc

---------- Post added at 03:47 PM ---------- Previous post was at 03:42 PM ----------

does not seem to work for me does not round up to the 9

[Cutter]

The formula I gave you does the "rounding up to the 9" before being multiplied.

Maybe time to show us an example file that shows clearly what you have and the results you want:

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[jamesfish]

attached I hope is the file.
Thanks for all this help is really great and very nice

[Ace_XL]

If I understand this correctly, use the following

In E4,
=IF($D4="","",ROUND((INT($D4*4.5)+LOOKUP(MOD($D4*4.5,1),{0,0.29,0.49,0.69},{0.29,0.49,0.69,0.99})),2))
and adjsut the highlighted multiplier accordingly for prices in Column F, G & H

See attached (highlighted in yellow). Does this work for you?

[jamesfish]

That works great thank you soo much. One question why when I put in a code it comes up with 0.00 or similar but your code the column is blank.
You have all been great A BIG thanks to the forum for the help