VBA Not Following Consistent Rounding Rules?
Jun 13, 2014
This Subroutine:
[Code] .........
Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2
The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer
However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?
I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.
View 3 Replies
ADVERTISEMENT
Jul 9, 2008
I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.
Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.
Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812
I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)
1) =MROUND(A2,0.001)
3) =ROUND(A2,3)
4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))
5) =EVEN(A2)
6) =ROUNDUP(A2,3)
7) =ROUNDDOWN(A2,3)
View 9 Replies
View Related
Feb 28, 2009
I have MS Office 2003 with Excel and am comfortable creating formulas and have fun making spreadsheets, but have little experience with multiple tabs. They frustrate me. I'd like to set up a file that would essentially be a year of bookkeeping. Everything in view would be income, outgo, anticipated expenses, and running cash flow for a month. I'd like 12 or 14 tabs (1 per month plus overview and recap capability). If I make any format changes in row height or column width, I'd like those changes to apply to all tabs without having to copy the new layout to each of the other tabs. I think that would run the risk of also copying one months expenses/income erroneously to another month. Is this possible? I'd like to stretch a column, add column or row and have the changes apply to all tabs.
Also, most of the time, I like to have a tinted background behind my lists, but if a cost item needs to be copied from a green "expense list" to a blue "accounts paid" list, the color comes along. Can I copy only the text, not the format?
View 6 Replies
View Related
Aug 31, 2006
i have attached a sample spreadsheet that i am having a real problem with. each period i have to produce a report showing the week and period-to- date figures for various sites. the info is held in Adaytum and a view is created in excel that pulls in the data. i then run various formulae to create the report.
the cells in blue are a linked view from Cognos/Adaytum (an external database program). the cells in green contain a SUMIF formula that works correctly until the Adaytum view is changed, whereupon all formula with cell references within the blue area turn to REF#. don't ask me why, it seems to be a quirk of this version of Adaytum i use
so, in the example attached if i change from period 06 to period 07 in the Adaytum view, my criteria cells (B2:E2) would go to REF# and the green cells (J5:J8) go to REF# also.
the only way i have found to get around this is to use a vlookup, and extend the lookup range beyond the blue cells, ie. =VLOOKUP($H5,$A$5:$F$8,2,0). for some reason this works!
now, for a single weeks info this works ok, as the lookup is referencing a single offset column but i can't think of a way of then saying, "do the lookup and then sum columns B+C, or B+C+D"
does anyone have any ideas how this might work? can the Sum or Offset functions be used within a Vlookup?
View 9 Replies
View Related
Nov 9, 2009
In the attached file I am simply trying to get a percentage for a given group based on the column "annual starts". When I try and copy down the formula for the other cells it gives me an error because its using the wrong denominator (the errors show up in the excel sheet as #div/0).How can I make this consistent?
View 2 Replies
View Related
Nov 30, 2006
Is it possible to apply some kind of formatting to a range, that will force all numbers entered into that range to have the same number of decimal places as the cell with the maximum number of decimal places?
View 9 Replies
View Related
Jan 4, 2007
I have 3 columns of data "Store", "Week" and "Cust" and use the code below to ensure that it was always 150 consecutive rows long regardless of whether any data is in the row.
This is fine if I just have one week to sort, but I find I now have 52 weeks of data and each week could have a different number of stores. I just need the macro below to run each time a new week starts. I can easily put a space (or something else) at the start of each new week so that a " loop" can check for it.
For i = 1 To 150
If ActiveSheet. Cells(i, 1).Value <> i Then
ActiveSheet.Rows(i).Insert
End If
Next
View 4 Replies
View Related
Oct 9, 2008
Newbie here. I have a very frustrating problem. I am using excel for my gradebook at school. I've tried several different ways to assign a letter grade to an number average. It works fine each different way I do it, VLOOKUP, IF, etc. But, I have about 5 grades out of 100 which give the wrong letter grade. For instance, my scale says that a grade of 85 should be lowest limit of a "B", but I get a "C" returned in the cell instead. Like I said, it only happens on a few grades. The biggest majority work fine. I can't figure out why. Any ideas? Attached is one of the "problems" with a student's grades. Note the Final Avg with a grade of "C", it should be "B".
View 12 Replies
View Related
Dec 1, 2009
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.
i.e.
lets say i have the following cells filled:
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1
etc.
what i need in the end is this:
1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7
View 9 Replies
View Related
Jan 11, 2007
I cannot determine why I am getting an N/A error in Cells I11:L11. The formula is copied and consistent throughout the sheet. The Table referenced in the formula appears to be fine. I just cannot figure this out.
View 5 Replies
View Related
Apr 11, 2012
I have been struggling to get dates to remain in a consistent format within my Excel 2003 worksheets. I would like to work out how to keep them permanently in the "dd/mm/yyy" format. I have tried several formatting options. The current method I will explain below:
I have several forms into which the 'date' is automatically entered using the - Format(Date, "dd/mm/yyyy") code. This is then saved into cells on a worksheet using the code
ws.Cells(iRow, 1).NumberFormat = "dd/mm/yyyy"
ws.Cells(iRow, 1).Value = CDate(Me.Date1.Caption)
At this point if I do a search for a date, lets say 23/03/2012 I can retrieve it and everything related to it - all seems fine (also all dates are visually in the correct format in my cells etc).
The problem arises when I exit Excel and save the workbook. When I go back into the workbook at a later time and try and search for the same date it won't work. Although the dates in the cells still read for example 23/03/2012, the search requires me to now use the format 03/23/2012 to retrieve the information (It seems like in the background the date has reverted to the US date format upon saving and yet visually it is still how I've set it up).
View 1 Replies
View Related
Jan 8, 2014
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
View 5 Replies
View Related
Dec 17, 2011
Is it possible to do the following with a formula in Excel...
I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.
The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.
USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago
Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?
View 9 Replies
View Related
Jan 8, 2009
I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.
View 8 Replies
View Related
Dec 9, 2013
How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
View 3 Replies
View Related
Jul 12, 2006
I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
View 4 Replies
View Related
Aug 5, 2006
I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).
View 9 Replies
View Related
Oct 9, 2013
how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.
View 3 Replies
View Related
Apr 12, 2014
I have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true
(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))
View 5 Replies
View Related
May 29, 2014
I have started to use excel again because I need to create a spreadsheet for maintenance hours done on a vessel. The problem I have right now is, how to set a rule to allow only certain multiple of a particular number to appear. For example,
I have a initial cell value where I have to manually insert the total number of hours that have gone by in the vessel. Let us say that this value is 500 hours.
In another cell which is labeled, "Last Completed", it would have to read at how many hours this job was last completed.
I also have another cell which gives you the time interval before the job is performed again. This value is 250.
So if the current number of hours is 500, the last completed date should be 250. (assuming Maintenance was done at 0 hours and 250 hours).
How do I make it so that the "Last Completed" cell would read 250 until I hit 750 hours which at that point should read 500 Hours.
View 14 Replies
View Related
Feb 11, 2009
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.
We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on
I use Excel ver 2003. Trust my query is reasonably clear?
View 7 Replies
View Related
Oct 28, 2009
I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry:
=CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3)
What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".
View 5 Replies
View Related
May 15, 2013
I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
View 5 Replies
View Related
Nov 27, 2013
I have got to this stage with a formula but cannot understand why it isn't working:
=IF(OR(A2=1,A2=2),(MIN(B2:B6)+50),(MAX(B2:B6)+50))
If A2 equals 1, I want it to add 50 on to the smallest (earliest) date in C2:C5.
BUT
If A2 equals 2, I want it to add 50 on to the largest (latest) date
The formula just adds 50 on the smallest (earliest) date, whether A2 equals 1 or 2.
What am I doing wrong?
View 5 Replies
View Related
Feb 18, 2014
I have a file which I want to copy the lines.
I have made a test file for clarity. See annex.
View 3 Replies
View Related
Aug 21, 2007
I am trying to clear row with the following rules:
the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here,
Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
View 9 Replies
View Related
Apr 4, 2008
I want something strange but i guess you can do just about anything with excel these days if you ask around.
I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:
sheetname = runxyy_s=zz%
Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
View 9 Replies
View Related
May 25, 2007
I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.
What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:
1) Permutation can't have both S and PP, so one or the other
2) Sum of H and HN must be greater or equal to sum of S and PP
3) H or HN or both must be present, so their sum cannot be zero
4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.
View 4 Replies
View Related
May 4, 2014
I had previously posted a query on deconstructing 2 set of tables (with similar heading)(in sheet 1 & 2) & combine them side by side with similar heading (in sheet3) so as to use condition formatting to highlight the differences & visually i can compare them easily.
1 group is 2 tables to compare. I have about 30 groups. So after creating the 1st group, i intended to use it as a template, without having to create the conditional formatting again & again. But after copying, the conditional formatting rules change. I am not sure why.
I attached a sample of my template for reference.
Sheet 1 - Table 1
Sheet 2 - Table 2 & part of Table 1
Sheet 3 - combine both Tables with similar column header side by side
in Table 3, cell B3 rules are "=C5<>B5"
SO the problem is when i copy data from Sheet 2, Table 2's Column Header B (cell f9 to f421) to Sheet 3, Column Header B2 (cell c5 onwards) the rules change to "=C5<>XFC5"
View 7 Replies
View Related
Oct 5, 2009
Ive been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies
View Related
