Round Prices to nearest Quarter, OR to .99

[Piktro]

I work for a company that has a bit of a strange pricing structure - basically how it works, is there are different membership levels, and each product has a price listed for each level. Example: Retail is $22, Member is $20, Investor is $18, Employee is $15, so on and so forth

We have to manually enter all of these prices, so I made an excel spreadsheet to calculate the approximate prices for each level (~55% profit for retail, ~50% profit for member, etc). I'm trying to automate the task of rounding to the nearest quarter, or rounding down even dollar amounts ($10.00) to $9.99 automatically.

The formula I've been working on is giving me a bit of trouble.

I've tried using nested IF statements for the >=.01, >=.25, >=.50, >=.75, etc - I've tried using the CEILING function to round it up, but then the .99 cent one stumps me. I've tried separating the integer, and I've tried using the RIGHT function to just isolate the decimal, and take it from there - but I just can't seem to get it to work. I'm over thinking it at this point, and can't figure it out.

My apologies if this post is a bit confusing, but I hope I explained what I need well enough for somebody to assist me. It would be greatly appreciated.

 

[Dryver14]

use the sum (wholenumber)-0.01

 

[Inzuri]

Data below starts in cell A1 (Full range is A1:B8):

[TABLE="width: 118"]
<tbody>[TR]
[TD="align: right"]$1.12[/TD]
[TD="align: right"]$1.25[/TD]
[/TR]
[TR]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.99[/TD]
[/TR]
[TR]
[TD="align: right"]$15.38[/TD]
[TD="align: right"]$15.50[/TD]
[/TR]
[TR]
[TD="align: right"]$14.51[/TD]
[TD="align: right"]$14.75[/TD]
[/TR]
[TR]
[TD="align: right"]$8.65[/TD]
[TD="align: right"]$8.75[/TD]
[/TR]
[TR]
[TD="align: right"]$8.70[/TD]
[TD="align: right"]$8.75[/TD]
[/TR]
[TR]
[TD="align: right"]$7.50[/TD]
[TD="align: right"]$7.50[/TD]
[/TR]
[TR]
[TD="align: right"]$9.90[/TD]
[TD="align: right"]$10.00[/TD]
[/TR]
</tbody>[/TABLE]

Formula in Cell B1: =IF(ISERROR(SEARCH(".",A1,1)),A1-0.01,CEILING(A1,0.25))

Copy down as needed.

HTH

Note: Above formula is assuming that the first column (initial prices) are numbers (i.e. 1.12, 1, 8.7, etc.). That's how I was able to get around the 1.00 to .99 issue that kept coming up.

 

[Piktro]

Thank you very much for your quick responses!

Inzuri - this was working for most of the items I was pricing, but I ran into an item who's cost was 8.99, which translated to a $17.98 price.
It rounded up to $18.00.

If I change the cost to $9.00, it works - and it will make the price $17.99, but only because the data in A1 is an even dollar amount.
Everything between X.76 and X.99 will round back up to X.00

Any ideas? I'm not so familiar with the Search functions.

Thank you!

 

[ashliiii]

In order for any $ amount to be converted to the nearest quarter, if cell A1 contains the $ amount. In cell B1 type the following formula

=IF(MOD(A1,0.25)<=0.12,A1-MOD(A1,0.25),IF(MOD(A1,0.25)<=0.24,INT(A1/0.25)*0.25+0.25,A1))

If you elaborate a little bit more, what you want for the rest, will try to help.

 

[Inzuri]

Thank you very much for your quick responses!

Inzuri - this was working for most of the items I was pricing, but I ran into an item who's cost was 8.99, which translated to a $17.98 price.
It rounded up to $18.00.

If I change the cost to $9.00, it works - and it will make the price $17.99, but only because the data in A1 is an even dollar amount.
Everything between X.76 and X.99 will round back up to X.00

Any ideas? I'm not so familiar with the Search functions.

Thank you!

So should the $17.98 price have rounded to $17.75? Because $18.00 is the nearest quarter.. are you wanting everything to end in either .25, .5, .75, or .99? How should the end costs look overall?

And the Search in my formula above is to look for the decimal place. If there is no . in the number it's checking, then it throws a #VALUE error.

 

[Piktro]

Yes, I'm looking for every price to end in either .25, .5, .75, or .99 - preferably rounding up (Ceiling).

I think I figured it out, I used a 2nd sheet to calculate the prices based off the cost I entered on the first sheet, then pulled the results back onto the first sheet.

in a1
cost data from first sheet
in b1
=CEILING(A1, 0.25)
in c1
=IF(INT(B1)=B1, TRUE, FALSE)
in d1
=IF(C1=TRUE, B1-0.01, B1)

It's messy, but it does do what I'd like it to do, for now.

 

[Inzuri]

Try this:

=IF(ISERROR(SEARCH(".",A1,1)),A1-0.01,IF(CEILING(A1,0.25)=INT(A1)+1,INT(A1)+0.99,CEILING(A1,0.25)))

On my test data above, it seems to do what you're looking for.

 

[FormR]

I think this also does what you want:

=CEILING(A1,0.25)-(MOD(CEILING(A1,0.25),1)=0)*0.01

 

[Piktro]

That's what I was looking for! Thank you!

Also, thank you all for your help - I actually learned a few other things as well.

I think this also does what you want:
=CEILING(A1,0.25)-(MOD(CEILING(A1,0.25),1)=0)*0.01