CEILING

[RD]

What would be the equivalent function in Access/SQL Server for CEILING(number, significance) in Excel? I have a requirement to create a pricing database that takes Total Cost and marks it up by a variable margin. It's variable in the sense that different parts get different margins. The following are the fields used to calculate total cost:

DIRECT MATERIAL DIRECT LABOR OVERHEAD TOTAL COST UNIT GROSS MARGIN


By entering the selling price they can analyze the gross margin for that part. Then based on their satisfaction with the Gross Margin they'll pass the selling price to me at I'll enter it into the "START" field of the following example. You'll notice that all discounts (VL1-4) are calculated off of the DEALER BASE value. The discounts for VL1-4 are predetermined and do not change. For example to calculate the price for VL1, the formula is =Dealer Base * .96

START MSRP RAW DEALER BASE VOLUME LEVEL 1 VOLUME LEVEL 2 VOLUME LEVEL 3 VOLUME LEVEL 4
$8,650 $10,816 $9,202 $9,205 $8,837 $8,745 $8,653 $8,393


The CEILING function is used by taking the RAW value ($9202) and rounding up so that the result ends in either 0 or 5. The formula reads =CEILING(C2, 5) where C2 equals the RAW value and 5 is the significance we round by.

Another swizzle to this is when they begin stacking the discounts, e.g. VL4= VL3 * .97. How should we tackle this?

In the end, I'd like to publish different price lists for each part for sale by discount level. For example, MSRP Price List, Base Price List, L1 Price List, L2 Price List, L3 Price List and so forth.

Any suggestions?

Reid Deputy

 

[Tom Ellison]

Dear Reid:

Your real name, at last!

To be used in a query, the function would need to be written as a
scalar User Defined Function for SQL Server, since that's your back
end. Functions from Excel, Access, or functions from your own modules
are not available.

I'm going to bed now, but I'll work a function up for this in the
morning.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Reid:

Here's what I cooked up as a simple approach:

CREATE FUNCTION fn_Ceiling5(@Value money)
RETURNS money
AS
BEGIN
DECLARE @Result money
SET @Result = CEILING(@Value / 5) * 5
RETURN @Result
END

It just takes the one parameter, as it always round up to the next 5.

Please note this works to 3 decimal places and beyond, but if you pass
it 100.0004 it returns 100, not 105. Don't know if that would cause
you problems. This one is based on money, which keeps only 4 places,
so when you divide 100.0004 by 5 you get 20.0000 due to loss of
significance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Reid:

After a bit of testing, I can offer a new, more general version of the
User Defined Function:

CREAE FUNCTION fn_Ceiling(@Value money, @Interval money)
RETURNS money
AS
BEGIN
DECLARE @Result money
SET @Result = CEILING(@Value / @Interval) * @Interval
RETURN @Result
END

The syntax for accessing this function, and what I used to test it,
is:

PRINT dbo.fn_Ceiling(9202, 5)

The PRINT is just for testing, the rest is the syntax to use within a
query.

As to your question on Stacking the Discounts, I would suggest that
each discount that is based on another discount be precalculated so
that its exact discount rate is already stored. While it would be
terrific to be able to lookup a "compound" discount rate recursively
at run time, such recursion is a bit difficult to program and may be a
bit slow at runtime.

To do this the way I am recommending actually violates the rules by
which we usually design databases, but this can sometimes be excused
in circumstances such as recursive requirements. While I have an idea
how this can be done, I don't feel I have quite enough detail of your
situation to be quite sure how I'd implement it. As a result, my
answer is going to have to remain somewhat general for now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts