I am suspecting that round function is playing a trick on me (N3 = Round(Rnd() * 10, 0) / 10 ). Although sometime it appear to be equal in fact they are not (Reponse = verifie) because of the round function
Sub auto_open()
Dim N1 As Integer
Dim N2 As Integer
Dim N3
Dim Reponse As Double
Dim Compteur As Integer
Dim Score As Integer
verifie = 0
Reponse = 0
N1 = 0
N2 = 0
N3 = 0
On Error Goto ErrorHandler
Score = 0
Randomize
MsgBox "Welcome in the mental calculation program "...............
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.
I am using Excel 2000. See the example attached.
In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)
I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this?
Eg
=ROUND(I36-I37,3-LEN(INT(Q16)))
in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007
I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation.
This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16.
Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing.
All cells in this formula are dates in the format dd-mmm-yyyy h:mm (except C1).
The formula works fine only if I type in a date/time like 09-Jun-2007 0:00 in cells A1 or B1. As soon as I revert to an actual date/time eg 09-Jun-2007 9:53, it returns a #VALUE! error.
I want to use the Round Function where i have a list of dates. I want the Round Function to count the number of dates in the list. I tried to use Round(C4:C16,0), but i didn't work.
1) My data is $42,397.40 is there a way that I can format for it to read $42.4?
I tried ROUND function and that did not work.=ROUND(A1,-3)
2) Cell A5 is Data:1/14/09 - 1/14/09. Since I only wanted the date, I used this formula =RIGHT(sheet1!A5,LEN(sheet1!A5)-FIND("-",Sheet1!A5)) which gave me 1/14/09. I need to set the format to look like 1/14/2009 but the data function would not recognize..
I'm trying to round some figures UP to the nearest .5, However i only know this formula: =MROUND(P2,0.5) and that rounds to the nearest .5? Is there anything i can use instead to make sure it is always up not down. so 6.13 becomes 6.50 or 7.53 become 8.00? etc.
Is there a way to add the round function to the current formula in multiple cells at once? I have a worksheet that has many cells that are linked to another worksheet or workbook, and I need all of the cells to be rounded to 2 digits. Can I do this with a macro?
formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data.
see examples below in Column A and the intended results in Column B.
with a formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data. see examples below in Column A and the intended results in Column B.
Column A row 1: Joe 1.234, Al 6.89656, row 2: Sam 6.5,
i have a list of minutes in cells a1-a5 say 123 256 147 158 235 divided by 60 giving a total of 15.3 hours. i want the hours to round up if over the. 5 mark or round down if under .5 how would i get the desired result?
A cell value is calculated via a formula in vba. I want to round the result down to the nearest odd number or down to the nearest even number, depending on conditions in an other cell. The result is already an integer.
rounding the numbers. I am working on a quote in which quantity is arrived by dividing the sell price by Total sell price. The condition is the result (quantity) should always be a whole number, I can achieve that by cell formatting but when the calculation is done using handheld calculator the results are different.
I need the result to be same if using excel or handheld device i.e quantity in whole number.
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
i want a cell to round itself. i have a form i'm filling out, based on a percentage of a dollar amount. when the formula calculates, it only shows the first 2 numbers past the dollar point. however, the cell still "knows" what the number is. I have several of these formulas on a spreadsheet, and the sum of them at the bottom of the page is NOT what would you would get adding up the numbers you see on the page, as it is calculating numbers you can't see.
I am trying to do is have the roundup formula round up the result of a more complex formula BUT do it all inside of the same cell? The formula I have is in cell A1 and currently I have to have the cell that contains the round up formula (in cell A2) and have it reference A1. The complex formula is =((280283.47/798186.89)*(700*20*4)) and the result is -19,664.41 which I want to round up to $20,000. Is there a way to make this all occur in just cell A2 or am I stretching it?
I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.
I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.
My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.