Rounding To Nearest Quarter
Someone posted a thread looking for a way to round a number to the nearest quarter (.25). This thread must have been deleted before I had a chance to respond. Here is how you do it:
Code: If (mynumber / 0.25) * 100 Mod 100 Then 'checks to see if number really needs rounding 'UpDown = 0.5 rounds up or down to nearest quarter 'UpDown = 0 rounds down to nearest quarter 'UpDown = 1 rounds up to nearest quarter UpDown = 0.5 mynumber = Format((Int(mynumber * 4 + UpDown)) / 4, "$0.00") Else mynumber = Format(mynumber, "$0.00") 'number did not need rounding End If
Harry W posted a solution as I remember, but it did not work correctly:
Quote:
Code: 'This code will Round down: dblValue = (Int(dblValue * 4 + 1)) / 4
'This code will round up: dblValue = (Int(dblValue * 4 + 2)) / 4
'This code will round to the nearest 0.25: dblValue = (Int(dblValue * 4 + 1.5)) / 4
You can try this little example out:
Code: Private Sub Form_Load() Dim mynumber As String Dim arr arr = Array(1.73, 1.25, 0.03, 0.15, 0.37, 0.92, 1.75, 1.5, 0.62, 2.38, 1200.23) For x = 0 To UBound(arr) mynumber = Format(arr(x), "0.00") 'Round up to a quarter If (mynumber / 0.25) * 100 Mod 100 Then 'checks to see if number really needs rounding 'UpDown = 0.5 rounds up or down to nearest quarter 'UpDown = 0 rounds down to nearest quarter 'UpDown = 1 rounds up to nearest quarter UpDown = 0.5 Debug.Print mynumber; " "; Format((Int(mynumber * 4 + UpDown)) / 4, "$0.00") Else Debug.Print mynumber; " "; Format(mynumber, "0.00") 'number did not need rounding End If
Next x arr = Array(vbNull)
End Sub
[Edited by dsy5 on 10152000 at 03:30 PM]
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Nearest Quarter Hour
Hi,
In the code below, LaborHours is a text box on the form.
Is there a better way to do this?
Code:
If LaborHours  Int(LaborHours) <> Abs(0) And _
LaborHours  Int(LaborHours) <> Abs(0.25) And _
LaborHours  Int(LaborHours) <> Abs(0.5) And _
LaborHours  Int(LaborHours) <> Abs(0.75) Then
MsgBox ("Labor Hours must be to the nearest 1/4 hour.")
End If
Thanks,
Al.
Rounding To The Nearest .5%
Hello,
Does anyone know how to round to half a percent?
eg
57.48 = 57.5%
56.43 = 56.5%
55.90 = 56%
51.70 = 51.5% (edited)
I did a search on rounding but only got to go up/down to a whole number
Thanks
Rhys
Rounding Off To The Nearest 5 In VB6
I have a currency amount that I always want to round off the the nearest 5. I would like the currency amount to round off downwards to the nearest five.
Eg. $459 = $455
Eg. $456 = $455
Eg. $451 = $450
Eg. $454 = $450
ie. going down to the nearest 5.
I am not at all experienced in VB6 so any help will be appreciated.
Rounding To The Nearest Whole Five
Hi Everyone,
I am wondering if anyone would know how to round a number to the nearest whole 5. I saw an example code online somewhere of being able to do it using the round() function but I cant find it.
Thanks everyone for the help!
Rounding To Nearest 25,000
This is not an ordinary rounding issue. Here's what I need to do. I have to round any number up to the nearest 25,000 increment.
For example:
124,698 would need to round to 125,000
126,125 would need to round to 150,000
1,278,189 would need to round to 1,300,000
Anyone know of a mathematical formula for doing this? Or any good ideas as to how to accomplish this? THANKS!
Rounding To Nearest .25
Hi,
I'm looking for a way to automatically round numbers and times to the nearest .25.
Right now I'm simply checking the numbers. e.g. if the decimal portion <> 00 or .25 or .50 or .75 then show a message box telling the user to round the input. Likewise, minutes are checked for 00 or 15 or 30 or 45.
Any ideas will be greatly appreciated.
Thanks,
Al.
Rounding To Nearest Eighth
Is there a function for rounding to the nearest increment (I need 0.125 in my case)? For example, Excel has a function called MROUND. Does VB have anything like this? I would like to stay away from iterative loops if possible.
I've searched and can only find information on general rounding. I have read many useful posts and saw an interesting thread regarding rounding to the nearest tens place. However, nothing seems relevant to my problem.
Thanks in advance!
Rounding Down To Nearest Nickel
Is there a vb function that would allow me to round down to the nearest nickel or should I just write something that will change it as a string?
Rounding Up To Nearest 50 Cents
Is there a simple way of rounding UP to the nearest .50 cents
eg
12.00 = 12.00
12.01 = 12.50
12.49 = 12.50
12.50 = 12.50
12.51 = 13.00
12.78 = 13.00
I can do it by writing a lot of ifs' etc.
Was interested if there is a simple way of doing this.
Appreciate any help
Rounding Currency To Nearest 5cents
G'day,
I have to round a currency total to the nearest 5 cents. Is there an inbuilt function to do it better?
VB Code:
Dim temp As Currency temp = TotalCost * 10  Int(TotalCost * 10) If temp < 0.5 Then TotalCost = TotalCost  temp/10 ElseIf temp > 0.5 Then TotalCost = Round(TotalCost + 0.05, 1) End If
Peter
**RESOLVED** Rounding To The Nearest Whole Number
Hi
I need help! I have conducted a search on this forum and have
found nothing.... what I need help with is rounding to the nearest
whole number ???
i.e
1.5 becomes 2.0
1.4 becomes 1.0
etc etc
Your help and time is very much appreciated
I Got A Good One ....Add Current Quarter Together
I have a text file that contains budgets:
it looks like this
Code:
Sep04 Oct04 Nov04 Dec04 Adj04 Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Total
Budget Budget Budget Budget Budget Budget Budget Budget Budget Budget Budget Budget Budget
_______ _______ _______ _______ _______ _______ _______ _______ _______ _______ _______ _______ _______
Product Name is here 0 0 0 0 0 4,961,364 4,934,910 4,907,408 4,882,032 4,861,383 4,851,790 4,847,268 34,246,155
Next Product...ect 0 0 0 0 0 4,961,364 4,934,910 4,907,408 4,882,032 4,861,383 4,851,790 4,847,268 34,246,155
I need to "grab" the product name (no problem) the second to last colum which is the current period/month (Also no problem)
but I also need to get the current quarter as a sum...
I am taking the file and stuffing it into a variable... I need the variable to look like this:
Product Name12,345,65782,345,678
where first is namecurrent period(in this case its Jul05)SUm of the current quarter
Now, the hard part is that this file changes each month.. colums shift over so its always showing a Year... So I need to somehow figure out which cols to add to make current quarter!! (July is easy since its the begin of this quarter)
understand!???
THANKS!
Finding Quarter By Date
Is it possible to find out what quarter it is passed on the date?
For example Jun2002. Is there a function that would say this is quarter 2?
Thanks.
Drawing A Quarter Circle
can some one show me how to draw four quarter circles.
I have tried everything apparently i dont understand how the circle method works
this draws a quarter circle from top to right
i need right to down, down to left and left to up.
if someone could help thanks
VB Code:
a.Circle (0, 1500), 1500, ,6.283185 , 1.570796
Rounding Numbers To 2 Decimal Places  How To ? (Rounding)
I am returning math to a variable (and/or a text box) BUT I only want to have two or three decimal places, that are rounded off.
I cant figure out how to round them. I've tried setting text box properties, calling 'as currency' but I dont know what else to do.
Thank you.
Writing Program Quarter Toss Need Help
I am writing a program that shows how a quart toss would work in the program I did the quater toss om the a txtbox txtOUt i need to then take the information from txtOut so i can keep tract of how many heads and tails in 2 different txtbox one is txtheads the other txttails
I am at a complete lost here is what i have so far
Dim quarter As Integer, tails As Integer, heads As Single, toss As Integer
Randomize
quarter = Int(2 * Rnd) + 1
If quarter = 1 Then
txtOut.Text = "heads"
ElseIf quarter = 2 Then
txtOut.Text = "tails"
End If
End Sub
could some one please give me some help on this
this programm is located in my vb book on page 247
chapter 5 summery
Please Help Building Up SQLs For Separating Out Year, Month And Quarter Data From A Table
Hello Ppl..
I want help regarding generating different SQL statements.....for the following kind of table......
Product/Parameter/ItmDes/jan'05/feb'05/05Q1/05Y/jan'06/06Q2/06Y.......
_______________________________________________________________
So what I want to do is to run queries to find out monthly, quarterly and yearly data separately.....
1) jan'05, feb'05, ..... jan'06, feb'06......are monthly data...
so what I want is (logically)
when month is selected on the form for the output data,
ssql = "Select s.*jan* AND s.*Feb* AND s.*Mar*...........& _
"INTO [MONTH]" & _
"FROM [" & Me.PCYcle & "] s "
but SQL does not support this kind of * statements. I am sure that month field will include Jan, Feb, Mar, that kind of string , but it can be for year 05, 06, 07....so not sure about the year part.
2) 05'Q1, 05'Q2, ..... 06'Q3, 06'Q4......are quarterly data...
so in this case,
when quarter is selected on the form for the output data,
ssql = "Select s.*Q*" & _
"INTO [QUARTER]" & _
"FROM [" & Me.PCYcle & "] s "
but SQL does not support this kind of * statements. I am sure that month field will include Q1, Q2, Q3, Q4 that kind of string , but it can be for year 05, 06, 07....so not sure about the year part.
3) 05'Y, 06'Y, 07'Y......are yearly data...
so in this case,
when year is selected on the form for the output data,
ssql = "Select s.*Y*" & _
"INTO [Year]" & _
"FROM [" & Me.PCYcle & "] s "
but SQL does not support this kind of * statements. I am sure that month field will include Y in all year data column, but it can be for year 05, 06, 07....so not sure about the year part.
Please help building up this kind of logic and SQL statements... I am not sure about the year part in columns, it can be anything....so can not specifically define columns like,
select s.05'Q1, s.06'Q2, s.07'Q3........
Because I am developing this application for all kind of input tables and they may have different year range, so need some kind of dynamic way to generate this.
Thanks in advance....
Round To The Nearest .25
I have a program that divides up values (like 2.5, 7, 5.25, etc..) and I need it to round to the nearest quarter of a whole (round to 1.25 or 1.5 or 1.75)
is there an easy way to do this?
Round Up To Nearest
i am dealing with currency variables and what i want to do is always round up to the nearest 10p.
if it was 33.42 then i want it to return 33.50 but if its already on a full 10p like 33.40 then do nothing.
thanks.
Round To Nearest 0.25
I saw rounding to the nearest 5 here. I tried that method to round to the nearest 0.25, but it gave me a divide by zero error. Is there any other way to round to the nearest 0.25? Thanks.
Nearest Integer
Is there a builtin function in VB6 for calculating the "nearest integer" of a noninteger number? For example,
Nearest Integer(2.4) = 2
Nearest Integer(2.5) = 3
Nearest Integer(2.8) = 3
Nearest Friday's
Hi there
Want to know how do I return a date of the nearest friday. At the minute if I enter a start date and number of weeks it returns a date exactly that number of weeks later. That is if I enter 23/08/2000 and then 1 week, i get 30/08/2000 back which is a wednesday(I think!!) what I want returned is fridays date 01/09/2000. How do I do this?
This is my function at the minute
Public Function CalcPeriodEnd(StartDate As Variant, NoWeeks As Variant) As Variant
On Error GoTo Proc_Err
Dim NewDate As Variant
CalcPeriodEnd = 0
NewDate = DateAdd("ww", NoWeeks, StartDate)
CalcPeriodEnd = Format$(NewDate, "dd/mm/yyyy")
'just to check output
'MsgBox Format$(NewDate, "dd/mm/yyyy"), vbOKOnly
Exit Function
Proc_Err:
ErrorHandler
End Function
Thanks in advance
Gin
Find Nearest AP
Hi,
I got a new project on WIFI technology. I am facing a problem. The problem is when user is accessing my desktop application through internet. I have to identify which Access point he is using. In other words I want to find out the current physical location of the member programmatically. Is there any way to find out?
jiten
Round Down To Nearest 5
Hi all,
I have a calculation that will return an integer between 1 and 120.
I want to be able to round the result down to the nearest 5.
ie if result = 78 then round down to 75
if result = 15 then stick at 15
any ideas anyone
many thanks
Date Of Nearest Monday
Our financial year begins on the Monday nearest the 1st of July. For example the current financial year started on Monday 30/06/2003 which is week 1. I have written a function that takes a date parameter and returns the current financial week. It is not working properly because I assumed that the financial year always starts on the 1st of July. This causes problems because when I subtract the number of days and then divide by 7 to get the week number I sometimes get week 53 because I did not subtract the correct start date of the financial year.
This is my function:
Code:
Public Function NextWeek(thedate As Date) As Integer
On Error GoTo errNextWeek:
Dim dtedate As Date, _
strYearStartDate As String, _
intDaysInYear As Integer, _
intCurrentYear As Integer, _
intCurrentWeek As Integer
'get current year
intCurrentYear = Year(thedate)
'first day of financial year
strYearStartDate = "01/07/" & CStr(intCurrentYear)
'need function here to calculate monday closest to 01/07
'get days gone by since beginning of year
intDaysInYear = DateDiff("d", strYearStartDate, thedate, vbMonday)
If intDaysInYear < 0 Then
strYearStartDate = "01/07/" & CStr(intCurrentYear  1)
'get days gone by since beginning of year
intDaysInYear = DateDiff("d", strYearStartDate, thedate, vbMonday)
End If
'get week number and drop decimal
intCurrentWeek = Round((intDaysInYear / 7), 0)
'return value we need to start at 1 not 0
NextWeek = intCurrentWeek + 1
Exit Function
errNextWeek:
MsgBox "The following error occurred in NextWeek: " & Err.Description & _
" : " & Err.Number, vbCritical
End Function
I really need help with this one...
Thanks
Finding The Nearest Server
Good Morning. Is there a way to find the name of the nearest NT server that a drive can be mapped to? Or at least the one that the current user is logged into?
Thanks,
Nancy
Reallocate To Nearest PowerOfTwo
Hello!
I'm checking if the dimension of an one dimenisonal array is a power of two with a function that i found in wikipedia. If the array is not a power of two then how can i fill the array atomatically with zeros to the nearest power of two??? For example if the array is (0 To 12) then it should become (0 To 15) filling the array with three zeros(at the end). Any ideas about a function like that????
Thank's!
Rouding To Nearest Number
Hello All
I am usin WMI to return the amount of memory on a system, but it is returning a value such as...
1023.997 Mb
I want it to display 1024?
How is this done?
Thank you
Round To Nearest Hundred...
okie dokie, i just need the simplest code/ function / anything to round to the nearest hundred. like if i have 1246 i want 1200 if i have 1256 i want 1300, no big deal, need it for school, but not neccasarrily just those two numbers yaknow ?
Nearest Numbers/multiples
How would I go about doing this:
First, you would need a line. The arrow keys move the X2 and Y2 properties around the form. I want them to move by increments of ten. When I press U, They both move to the nearest multiple of 120. So if X was at 1210 and Y were at 1275, X would equal 1200 and Y would equal 1320.
How Do I Find The Nearest Number?
I have another problem with Access, i want to find a row based on the closest number i.e.
these are my numbers, they are set but there is no set pattern to them:
112
128
132
145
156
163
my problem is , if someone puts in 150 in the [Page Count] field, i want it to put 145 in the [Counter] field because this is the closest number
I used to use INDEX() and MATCH() on Excel but of course this does not work with Access
Thanks
Emma
Round Down To The Nearest Smallest Possible Multiply
VB Code:
Public Function rd(number as long, multiply as long) as longIf number = 1 Thenrd = 1Elserd = Int((number  1) / multiply) * multiply + 1End If End Function
This is what it will return
rd(1, 9) = 1
rd(2, 9) = 1
rd(3, 9) = 1
rd(4, 9) = 1
...
rd(9, 9) = 1
rd(10, 9) = 10
rd(15, 9) = 10
rd(18, 9) = 10
rd(19, 9) = 19
rd(20, 9) = 19
...
it rounds down to the nearest smallest multiply of 9 and add 1 to the result.
i already got the function working, but i was wondering whether there is a faster way of doing it, since the function is going to be called alot
thanks in advance
Round Time To The Nearest 15 Minutes?
Does anyone have a lil function / know how to round time to the nearest 15 minutes?
I have broken the day up into 15 minute intervals, and I would like to be able to default to the current interval.
ie:
08:40 = 08:30
06:16 = 06:15
Does this make sense?
Thanks alot for your help.
Round Time To Nearest 15 Mins
Hi,
Does anyone know how I could round the minutes to the nearest 15 minutes, I'm using this
%=minute(now()) %>
Regards,
Sanj
How To Find Nearest Access Point
Hi All,
I got a new project on WIFI technology. I am facing a problem. The problem is when user is accessing my desktop application through internet. I have to identify which Access point he is using. In other words I want to find out the current location of the member. Is there any way to find out?
PLease Let me know if you have any solution. It is urgent.
jiten
Edited by  jitendash on 11/17/2004 2:08:56 AM
Return The Nearest Or Exact Integer In A Listbox
Hello All
I am trying to write a function that will return the nearest or exact integer in a listbox.
I have a VB6 form with the following:
 A command button
 A textbox
 a listbox (loaded with numbers from 0.01 to 10)
Click on the command button will generate a number between (0.0110) and input the number into the textbox.
What I am try to do here is to match this number with the same number in the listbox or the nearest number in the listbox.
I tried to add the line below to the command button but it didn't work
Code:
List1.Text = Text1.Text
I found a function that searches a listbox but this also didn't work
Code:
Option Explicit
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Integer, _
ByVal wParam As String, lParam As Any) As Long
Const LB_FINDSTRING = &H18F
Private Sub Text1_Change()
List1.ListIndex = SendMessage(List1.hwnd, LB_FINDSTRING, Text1, ByVal Text1.Text)
End Sub
The function above only works when I type the number generated in the textbox
I don't know if there is a way to automate this
Could you help
Thanks
Return The Nearest Or Exact Integer In A Listbox
Hello All
I am trying to write a function that will return the nearest or exact integer in a listbox.
I have a VB6 form with the following:
 A command button
 A textbox
 a listbox (loaded with numbers from 0.01 to 10)
Click on the command button will generate a number between (0.0110) and input the number into the textbox.
What I am try to do here is to match this number with the same number in the listbox or the nearest number in the listbox.
I tried to add the line below to the command button but it didn't work
VB:

List1.Text = Text1.Text

I found a function that searches a listbox but this also didn't work
VB:

Option Explicit
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Integer, _
ByVal wParam As String, lParam As Any) As Long
Const LB_FINDSTRING = &H18F
Private Sub Text1_Change()
List1.ListIndex = SendMessage(List1.hwnd, LB_FINDSTRING, Text1, ByVal Text1.Text)
End Sub

The function above only works when I type the number generated in the textbox
I also tried using this
VB:

Option Explicit
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Integer, _
ByVal wParam As String, lParam As Any) As Long
Const LB_FINDSTRINGEXACT = &H18F
Private Sub Command1_Click()
List1.ListIndex = SendMessage(List1.hwnd, LB_FINDSTRINGEXACT , Text1, ByVal Text1.Text)
End Sub

but nothing happens
I have my textbox reading 0.5 and my listbox with a range of 0.0110
I would expect the listbox to give the exact or the nearest value but it doesn't work
Am I missing anything out?
I am a beginner, I don't know if there is a way to automate this
Could you help
Thanks
How Can I Search An Array For The Closest Or Nearest Numerical Value In VB?
How can i search an array for the closest or nearest numerical value. The value that I will want to search for will be entered into a textbox, and the answer should be displayed in another textbox.
I'd have thought this would be a common operation, yet ive struggled to find any functions in VB or any other Info through google.
I know that want i to do is acheivable using MS excel and the lookup functions, but I would like this macro to be able to run on other office packages.
Brain Pain... Round Up Time To Nearest 5 Minutes ?
Anyone help out a poor chap withbrain pain ? I simply cannot think of the algorythm to round up the current time to the next five minutes..
humble apologies for such a crass post but all help gratefully received !!!
Chubby..
Determining Current "Quarter"...
I need to know the best way to get the current quarter date range based on current month...
I am filling an excel sheet...
one section is current quarter..
so if its June
I need to add April, May , June
if its July... then just add July...
the months are in an array (which is current month to current month 12)
auugh! my brain hurts trying to figure this out...
So if its June, I need to add values to a range in excel
April, May, June (for column headers)
I hope I have explained this well.. probably not since my brain is all over trying to figure this out.
[EDIT]: No im wrong!!
I will need to know what 1, 2, or 3 months to add together!! not fill out cells.. omg this sucks!
VB6 And SQL Server "Year, Quarter Year Dates"
Hi every one again.
This is dates again. The Vb and Access is the format I used to retrive records in a graph. I have tried changing it to SQL Server. Int is not recognized. On line help talks about "Q4 FY 2004" but don't know if i'm in the right direction.
Any help would be appreciated.
VB6 and Access
GROUP BY (Int (MachPeriod)), Format (MachPeriod,'dd/MM/yy ,ddd')"
VB6 and SQL Server
GROUP BY (Int (MachPeriod)), Convert (Varchar (20), MachPeriod , 103, ddd)
VB6 and Access
GROUP BY (Year ([MachPeriod]) *12 + Month (MachPeriod)1), Format (MachPeriod,'MMM, YY')"
VB6 and SQL Server
GROUP BY (Year (MachPeriod) *12 + Month (MachPeriod)1), Convert (Varchar (20), MachPeriod, Q4, FY)"
Thanks
Pieter
Rounding
Does anyone know why this is happening? When my colleague e mails me a spreadsheet it rounds differently to my sheet, even though I am viewing them both on my pc in my excel (we're both on excel 2000)
thanks
Rounding Off
Hi All
I am having a small problem and was wondering if someone could please help me.
When i enter a number into a cell, for example 7.8, it rounds it of to 8.0. How can i stop this from happening as i need 7.8 to be displayed.
I've been to Format/Cells/Number and have got it to display 1 decimal place, but i don't want it to be rounded off
Many Thanks in advance
