How to round currency values to a number divisible by another number?

[Eric]

I am using excel for my pricing. I am taking my base cost and fixing a
margin to it. After this I want to round-up the total price such that it is
evenly divisible into cents rather than fractions of cents when divided by
the number of units in total. This is because I need to show unit pricing
and total pricing.

For example, I have a base cost of $123.75 for a 55 gallon drum. I mark it
up 28% which comes to $171.875. I add my freight of $8.10, then round the
total to two digits and come up with $179.98. But, I have to show unit
pricing and there are 55 units in this container, which comes to $3.2723 per
unit. I want to round my price up so that my final price is based on the
margin I need, but also so the UNIT pricing is the nearest whole cent from
the total price after my margin. Is there a formula in excel that will
allow me to do this somehow??

 

[Frank Kabel]

Hi Eric
use the following formula if your value 171,875 is stored in cell A1
=ROUNDUP(A1/55,2)*55

or replace A1 with your formula to calculate your price

 

[Ron Rosenfeld]

I am using excel for my pricing. I am taking my base cost and fixing a
margin to it. After this I want to round-up the total price such that it is
evenly divisible into cents rather than fractions of cents when divided by
the number of units in total. This is because I need to show unit pricing
and total pricing.

For example, I have a base cost of $123.75 for a 55 gallon drum. I mark it
up 28% which comes to $171.875. I add my freight of $8.10, then round the
total to two digits and come up with $179.98. But, I have to show unit
pricing and there are 55 units in this container, which comes to $3.2723 per
unit. I want to round my price up so that my final price is based on the
margin I need, but also so the UNIT pricing is the nearest whole cent from
the total price after my margin. Is there a formula in excel that will
allow me to do this somehow??

Perhaps something like this:

=ROUNDUP((Cost/(1-Markup)+Freight)/NumUnits,2)*NumUnits

By the way, using your numbers, I would calculate a price for the drum of
$180.40 or $3.28 per gallon.



--ron