Use ROUND within custom function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ruthvofmc@hotmail.com - 07 Sep 2006 23:05 GMT

I created a function to calc the % of markup, it works just fin until I
get into prices >99.99, and then it doesn't match to some current data
I'm using that I think is rounding. Have I built this function
properly? and if so how do I get it to round?

Function Markup(Price)
'Low to High price range * % + flat additional amount

If Price <= 9.99 Then Markup = Price * (400 / 100 + 1) + 5
If Price > 9.99 And Price <= 49.99 Then Markup = Price * (325 / 100
+ 1) + 15
If Price > 49.99 And Price <= 99.99 Then Markup = Price * (275 /
100 + 1) + 25
If Price > 99.99 And Price <= 749.99 Then Markup = Price * (230 /
100 + 1) + 60
If Price > 749.99 And Price <= 1499.99 Then Markup = Price * (150 /
100 + 1) + 100
If Price >= 1500 Then Markup = Price * (100 / 100 + 1)

End Function

Reply to this Message

Bob Phillips - 07 Sep 2006 23:59 GMT

You don't need to test both ends of the range each time

Function Markup(Price)
'Low to High price range * % + flat additional amount

If Price <= 9.99 Then
Markup = Price * (400 / 100 + 1) + 5
ElseIf Price <= 49.99 Then
Markup = Price * (325 / 100 + 1) + 15
ElseIf Price <= 99.99 Then
Markup = Price * (275 / 100 + 1) + 25
ElseIf Price <= 749.99 Then
Markup = Price * (230 / 100 + 1) + 60
ElseIf Price <= 1499.99 Then
Markup = Price * (150 / 100 + 1) + 100
Else
Markup = Price * (100 / 100 + 1)
End If

End Function

but that apart it seems okay. What is wrong from your perspective.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> I created a function to calc the % of markup, it works just fin until I
> get into prices >99.99, and then it doesn't match to some current data
[quoted text clipped - 16 lines]
>
> End Function

Reply to this Message

Dana DeLouis - 08 Sep 2006 00:45 GMT

> prices >99.99, then it doesn't match current data

I would be totally guessing here, but it appears to me that your equations
don't follow a normal markup equation.
What I mean is that each range is linear, and "usually" each adjourning
range intersect at the changeover point.
A quick graph shows that none of your equations intersect at the changeover
point.
Again, I may be wrong for your situation.
For example, the 2 equations just below 100, and just above 100 are as
follows.

Price*(275/100 + 1) + 25
Price*(230/100 + 1) + 60

If I were to set these equal to each other and solve for Price, they
intersect at 77.78.
I would expect these two equations to intersect at 100.
Again, I'm just making a wild guess here.

Signature

Dana DeLouis
Windows XP & Office 2003

>I created a function to calc the % of markup, it works just fin until I
> get into prices >99.99, and then it doesn't match to some current data
[quoted text clipped - 16 lines]
>
> End Function

Reply to this Message