Need Formula for Rounding Up to PRESPECIFIED tenths/hundreths

[JayWilson]

Hi There. I'm looking for an excel formula that, instead of rounding up to the next whole amount, it will round up to a SPECIFIC pre-selected tenths and thousandths place "cents." See below first column is a sampling of random prices prior to an increase. The second column is using the roundup formula for a 5% increase. The third is the column I WANT my formula to provide. The first 10 are to end at .97 and the last two are to end at .42. In this case I did it manually, but is there some way excel can do it?

=ROUNDUP(A2*1.05,2)
[TABLE="width: 522"]
<colgroup><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD] Pre Increase Price [/TD]
[TD] Roundup [/TD]
[TD] To specific Ending Post Increase [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 325.98[/TD]
[TD] $ 342.28[/TD]
[TD] $ 342.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 150.98[/TD]
[TD] $ 158.53[/TD]
[TD] $ 158.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 145.98[/TD]
[TD] $ 153.28[/TD]
[TD] $ 153.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 205.98[/TD]
[TD] $ 216.28[/TD]
[TD] $ 216.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 99.98[/TD]
[TD] $ 104.98[/TD]
[TD] $ 104.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 251.98[/TD]
[TD] $ 264.58[/TD]
[TD] $ 264.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 199.52[/TD]
[TD] $ 209.50[/TD]
[TD] $ 209.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 149.37[/TD]
[TD] $ 156.84[/TD]
[TD] $ 156.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 155.22[/TD]
[TD] $ 162.99[/TD]
[TD] $ 162.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 242.88[/TD]
[TD] $ 255.03[/TD]
[TD] $ 255.97[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 255.40[/TD]
[TD] $ 268.17[/TD]
[TD] $ 268.42[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 245.40[/TD]
[TD] $ 257.67[/TD]
[TD] $ 257.42[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Chameleon64]

In B2:

=INT(ROUNDUP(A2*1.05,2))+IF(ROW()<=11,0.97,0.42)

 

[JayWilson]

Awesome! That certainly seems to accomplish my goal. Thank you so much, but I'd better understand the logic. What do the INT and and the +IF(ROW()<=11,0.97,0.42) actually do? Can you give me a laymen's explanation? I also tested changing the .97 to .99 and the .42 to .45 because these are hypothetical, not sure what we will settle on and it still works.

Thank you so much!

 

[Chameleon64]

Sorry for the delay in replying. INT (which is short for "integer") just removes the cents from the ROUNDUP value to leave a whole dollar figure. We can then calculate whether we need to add 0.97 or 0.42 dollars (i.e. 97 or 42 cents) by checking which row the value is in. If the row number is less than or equal to 11 then add 0.97 dollars, otherwise add 0.42 dollars. The ROW() function calculates which row the formula is on.