Roundup 1

[b3026]

Is it possible to roundup a number calculated via a formula? Example: My formula currently reads:

=Round([Price]/.6)

Can the answer be rounded up to the nearest 10? In other words, we need an answer of $14.20 to round up to $20.00. $208.00 needs to round up to $210.00.

All help will be appreciated.

 

[Blorf]

Hi. Try something like

X = Round([Price] / 0.6)
If Int(X / 10) <> X / 10 Then
Z = (Int(X / 10) + 1) * 10
Else
Z = X
End If

ChaZ

 

[GingerR]

=Int((([Price]/0.6)+10)/10)*10

 

[Blorf]

GingerR, what if Price/.06 results in a number that can be devided by 10? Then your number would change say 220 to 230, thats why I used the if statement.

ChaZ

 

[GingerR]

ha you're right. I'll have to fire my tester )

=IIf(([Price]/0.6)=Int([Price]/0.6),[Price]/0.6,Int((([Price]/0.6)+10)/10)*10)

g

 

[Blorf]

Cool.

Tell your tester I said Hi!

ChaZ

 

[b3026]

Thank you so much for the help. I tried the statement from GingerR and it worked great.

=IIf(([Price]/0.6)=Int([Price]/0.6),[Price]/0.6,Int((([Price]/0.6)+10)/10)*10)

One more question though -
The formula is used in a report. I found two instances, so far, when the number does not round to the next 10.
One has a starting price of $45.00 and using the formula above calculates to $75.00.
The other has a starting price of $165.00 and calculates to $275.00.

Any idea why this happens.

 

[MichaelRed]

Public Function BasRound2Val(Number As Double, Multiple As Single) As Double

    'Michael Red.    2/16/2002.  To Round UP to the Multiple

    'Usage: ? BasRound2Val(1.001, 0.25)
            ' 1.25

    'Set up variables
    Dim dblDivided As Double
    Dim intDivided As Integer


    dblDivided = Number / Multiple      ''Divide

    intDivided = Int(dblDivided)        'Integerise

    intDivided = Round(dblDivided, 0)   'Round

    'Return result, returning to nearest multiple
    If (intDivided = dblDivided) Then
        BasRound2Val = (intDivided * Multiple)
     Else
        BasRound2Val = (intDivided * Multiple) + Multiple
    End If

End Functio

MichaelRed

 

[PaulBricker]

Just to add to the list, here is a way to do it fairly simply

Int(((Int(myField) / 100) + 0.05) * 10) * 10


Paul

 

[PaulBricker]

Oop!!I'll have to fire my tester too. below 5 rounds to the lower 10. I'll have another look.

Paul

 

[GingerR]

b3026--cause again i didn't test for every possible thing.

try this:

=IIf(([Price]/0.6)=Int([Price]/0.6) And ([Price] Mod 10=0),[Price]/0.6,Int((([Price]/0.6)+10)/10)*10)

 

[PaulBricker]

Sorry, I can't help but work on those one liners. If anyone wants to try and test this, it seems to do the job.
Substitute any value up to 2 decimal places for nVal (if you need more decimals, you have to add more 9's to .999).

Int(((-1 * Int(-1 * (nVal + 0.999)) / 100) + 0.09) * 10) * 10

10 returned 10
10.01 returns 20
19.99 returns 20

Seems to work.

Paul

 

[GingerR]

but 220 returns 230!! Those damn testers!!

 

[PaulBricker]

Interesting because 220 returns 220 when I run it in a vba module (I just reran it again along with a bunch of other values). 220.01 returned 230 but 220 returned 220. If you would be so kind as to double check on your end I would appreciate it. I don't recall all the values I tested, but I did test a whole group of numbers that were divisible by 10 without problems.

Thanks very much.

Paul

 

[Blorf]

My function works. My tester is still employed.

Hahahahahaha

 

[GingerR]

i just copied your line and pasted it into a text box on a form. make a second text box, called it nVal.

Also, if I put in 19, I get 20. Good.
But if I put in 19.5 or any other decimal between 19 and 19.99, i get 30. Same for 29.x (40).

I also put a button on the form, plopped it in as code, push the button, same funky results.

Same results for Acc2002 and Acc97, Win2k.

Maybe you pasted the wrong code in the forum? Are you sure it's the same as in your module?

 

[PaulBricker]

No, I just copied the code from my post into a A2003 database and everything ran fine. I run A2000 at the office and haven't used my home version much yet but in both cases, everything checked out.

19.5 rounded to 20
19.55 rounded to 20
as well as all the other values you mentioned.
I appreciate you looking it over for me. Can't delete it and can't edit it os I guess it's buyer beware.

Paul

 

[PaulBricker]

If you don't mind humoring me a little more, can you tell me how you are testing it. I've got mine in a function

Function nFun(nVal As Double)
nFun = Int(((-1 * Int(-1 * (nVal + 0.999)) / 100) + 0.09) * 10) * 10



End Function

and then I'm calling it from the Immediate (debug) window using
?nFun(19.5)

Just wondering if the problem somehow crops up is used in a query expression or something like that.

Paul

 

[GingerR]

me testing = see my last post.

New Test = i ran it like you asked me to in your last post, results are as you explain.

so i put another text box on my form, and put in =nFun(nVal) where like I said before, nVal is just an unbound text box on my form where I'm putting test values. This new text box calling the function runs fine. Must have to do with the data type...

Have you duplicated my test and found my results?

In a query, the results are the same as my previous ones (honked up) when i just put the forumula as a calculated control. I made a table with four diff number types: long, decimal, double, integer. funky results all around.

wierd....

 

[PaulBricker]

Sorry, I missed you earlier explanation. I will try and duplicate all your tests. Will have to post results in the morning, but again, I do appreciate the help.

Paul