Rounding Off A No. With Limitation
Sep 20, 2007
Here it is:
I used ceiling and floor for this but it was futile.
I was hoping to round off a no. according to the limitations set,
For example:
Sample Entries Preferred Rounded-off nos.
Cell A1 Cell B1
.40 .40
.39 .40
.38 .40
.37 .35
.36 .35
.35 .35
.34 .35
.33 .35
.32 .30
.31 .30
.30 .30
When using ceiling and floor formulas there was an error prompt telling me that there are too many arguments.
View 9 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 26, 2009
I need to use the "IF" formula on a worksheet with 15 variables. There is a limit of 7 that can be used at once, does anyone know a way around this?
View 6 Replies
View Related
Jul 23, 2009
I have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))
View 14 Replies
View Related
Jul 20, 2006
I have a spreadsheet with a drop down box with 30 names in it. On the other worksheets of this file I have budget figures.
What I need is a correct formula so that when I select a name from the drop down box it will grab the figure from a cell in another sheet and place it into the sheet where the drop down box is.
Is it possible to use a formula which grabs more than one cell?
I have been using this formula below:
=IF(B5=1,NNO!C8,IF(B5=2,NSO!C8,IF(B5=3,NWE!C8,IF(B5=4,NCN!C8,IF(B5=5,NCS!C8,IF(B5=6,NHO!C8))))))
It works for one cell at a time- but only allows 7 variables at a time and I have 30.
Which means I would need 4 or 5 drop down boxes in stead of one.
View 8 Replies
View Related
Sep 6, 2007
Please see the attached GIF.
I am trying to take information from columns J ("Family Size") and L ("Annual Income"), and figure out how many units should be assigned to columns N ("Very Low Income Units") and O ("Low Income Units").
Essentially, this is what I want to do:
If J2 = 1 and N< 22850, then N2 = F2 and O2 = 0.
If J2 = 2 and N< 26100, then N2 = F2 and O2 = 0.
If J2 = 3 and N< 29400, then N2 = F2 and O2 = 0.
And so on through "8."
I looked at the Nested IF Limitation suggestions on the FAQs . . . it didn't seem like CHOOSE or VLOOKUP would get me where I need to go. Unless VLOOKUP is somehow merged with IF. Does anyone know the easiest way to get this done?
Steven Auto Merged Post;Oops . . . forgot to attach. See attached.
View 9 Replies
View Related
Sep 11, 2013
i have couple of textbox in my userform and i want to limit them for 7 number and one letter e.g. 7777777X.
View 2 Replies
View Related
Feb 20, 2014
I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function.See Below... The function works if i use 7 IF statements.
=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),
IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),
IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),
[Code] ............
View 9 Replies
View Related
May 8, 2008
i have tried to create a userform thru which data is to be entered. so that the data will be printed on a worksheet. here there are few text boxes, in which the number of digits should be equal to 14. after filling the userform when i click the print button if it is less than or more than 14 a pop up msgbox should be displayed with OK button and the cursor should go back to that particular text box. i have written the following code, but it has a problem. even if the total no of digits are 14 the msgbox is displayed.
say the text box name is Roll no
If txtRollNo.MaxLength 14 Then
MsgBox "Roll No should be of 14 digits", vbInformation + vbOKOnly
txtRollNo.SetFocus
Exit Sub
End If
View 9 Replies
View Related
Sep 2, 2009
I am working on Microsoft Office Standard Edition 2003 excel and am having trouble with my coniditional formats. I need to have 4 but can only have 3. Is there a simplae way around this? if not let me describe what i want to do and maybe there is a coded solution.
In columns N, R, V and Z i will have % increase in sales over a period. Below 0% would be red, between 0-5% is Amber, 5-10 is Green and over 10% is Purple. Is there a simple code that can be applied to this?
I can define the paramteres in excel in the row columns below.
ABC
1Range1Range2
2red0%
3amber0%5%
4green5%10%
5purple10%
View 9 Replies
View Related
Feb 15, 2010
I had a happy macro that copy and pasted sheets in an output folder for all excel files from a target file that included the desired changes. it worked fine. then i realized that when pasting a sheet, the text is limited to 250 characters, so an important portion of my sheet (some instructions in various cells) was not coming over. So i created the following which will copy the columns, but some of the row formatting is different and doesn't line up. my questions:
-is there a better way around this text limitation?
-if not, is there a way to copy the row formats for this section and include in my macro?
-is there a way to copy the cells (all or a range) themselves while using "ThisWorkbook", thereby including all the appropriate formating? it seems to not like to do that.
‘Copy columns from target sheet
Sub Perf_rev_subfolder_replace()
'Update the constant here if necessary
Const sOutputFolder As String = "M:ODPerf review2010 oolsTest folder est"
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
For Each fl In CreateObject("scripting.filesystemobject").GetFolder(sOutputFolder).Files
If InStr(fl, "3.0") > 0 Then
With Workbooks.Open(fl..............................
View 2 Replies
View Related
Aug 2, 2006
I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.
I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?
Sheets("main").Select
ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text
....
email.Body = messagebody
View 6 Replies
View Related
Apr 27, 2007
I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.
Example:
Sheet1
A1 (text string value) = LOWER
B1 (text string value) = L1
C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)
A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.
Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.
View 9 Replies
View Related
Jul 14, 2009
how do i set the macro to read a range without limitation?
currently i have a recording macro that only reads to P126 - if i end there data in C127:N127 I would like the macro to recognized that there is new data and update to P127 with the new calculation....
View 9 Replies
View Related
Oct 20, 2006
I was sure it would be in your archives but couldn't find it - apologies if this is a FAQ.
I am trying to extract the data in a spreadsheet to a bunch of files, one file per cell. The cells I need have very long text strings in them, actually whole HTML files.
Using this
Dim HTML As String
HTML = Cells(RowNdx, ColNdx).text
I can only read 1024 bytes - not nearly enough. Is there something else I can do to read the cell into a string variable?
View 4 Replies
View Related
Nov 2, 2006
I want to use the Camera facility to show a 13 column by 19 row section of a large report in another WorkBook. I have outlined the area I need copied .... clicked the Camera icon .....but when I enter the data in the receiving sheet I keep getting the message ...." The Picture is too large and will be truncated" Is there a maximum size that can be transferred
View 2 Replies
View Related
Mar 27, 2014
I often end up in a scenario where I want to be able to move text from one column into another using a formula, which involves pasting a formula down a filtered range... E.g. Column A contains both ID numbers & dates, I want to move ID numbers into Column B, so I apply a filter to all ID numbers using a formula "=A1" and paste it down the filtered range.
It becomes an issue when there is greater than 50 - 60 thousand rows - excel throws an error "selection range is too complex" and disregards the filtered range.
Only thing I can think of would be a VBA script to automate pasting a formula in blocks of 50,000 rows.
View 6 Replies
View Related
Feb 4, 2014
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Code:
Dim row As Long
Dim refname
row = 3
Do While Cells(row).Value ""
[Code] .....
View 3 Replies
View Related
May 8, 2014
Excel 2007-2010. I'm using match(string, range,0) but there must be a limitation on the length of the string since I know the string is in the range but it returns #value as if it is not found. Is there a VBA solution to get around this without having to loop/cycle through the entire range?
View 4 Replies
View Related
Jan 21, 2014
Excel 2010. There is a limit to the usability of the
=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
View 7 Replies
View Related
Dec 28, 2006
I am trying to do an "If" statement but because of the limitation of seven nested "IF" statements.
Here is what I would like to do:
=IF($C$9="HC",'Rate Matrix'!F$5,IF($C$9=0,'Rate Matrix'!F$6,IF($C$9=1, 'Rate Matrix'!F$7, IF($C$9=2,'Rate Matrix'!F$8, IF($C$9=3,'Rate Matrix'!F$9, IF($C$9=4,'Rate Matrix'!F$10, IF($C$9=5,'Rate Matrix'!F$11, IF($C$9=6,'Rate Matrix'!F$12, IF($C$9=7,'Rate Matrix'!F$13, IF($C$9=8,'Rate Matrix'!F$14, IF($C$9=9,'Rate Matrix'!F$15, IF($C$9=10,'Rate Matrix'!F$16, IF($C$11=0,'Rate Matrix'!F$17, IF($C$9=12,'Rate Matrix'!F$18, IF($C$9=13,'Rate Matrix'!F$19, IF($C$9=14,'Rate Matrix'!F$20, IF($C$9=15,'Rate Matrix'!F$21)
View 11 Replies
View Related
Jul 23, 2009
What is the code to round up a range of numbers at certain decimal.
View 14 Replies
View Related
Jan 30, 2008
I have a sheet that i use for doing quotes for sound systems. i have a qty and price column that i input and then a column which gives me the overall total. ie qty 2 x unit amount 160.00 = 320.00 i also have a discount column that i use and a column that gives them the final price after discounts. so i will put 22% into my discount column and get 249.60 as my final price for the 2 items after the discount.
what i am wanting to do is "round up or down to the nearest 5.00 mark. so for instance, the final price in this case would be 250.00 is this possible?
View 9 Replies
View Related
May 23, 2009
I have a worksheet (Sheet1) that gets information from other sheets (1)
So in cell D7 I have the function ='1'!K33
K33 is from the Sheet labeled (1) obviously. Now the problem I am having is if the number 20.6, it automatically rounds up to 21. I need to number to round down to 20 regardless if its 20.1, or 20.9.
Since I already have a formula in D7, how do I still get the information from the other sheet but have the number round down for me. Is it possible?
View 11 Replies
View Related
Jan 7, 2010
Am currently calculating the total number of cartons used for different items with this formula:
=SUM(F99/352)+(H99/135)+(J99/110)+(L99/60)+(N99/28)
The divisors are the number of items in a carton.
Would like to round up each of the 5 subtotals in the above formula to the next full number, so that the total will be the sum of full numbers.
What would be the correct formula?
View 4 Replies
View Related
Apr 21, 2008
Cell "CostPerEvent" has the value .298896, and when I run the following statement I expect that A2 will contain the same value .298896, yet it is entered into the cell as .3
Cells(1, 2).Value = Range("CostPerEvent").Value
Is there some kind of implicit rounding going on? I rewrote the code to:
Cells(1, 2).Value = CDbl(Range("CostPerEvent").Value)
But I am curious whether this behavior is documented...
View 9 Replies
View Related
Jan 16, 2007
I have written a macro which includes a number of calculations. This has resulted in a value with a number of decimal places. I want this value rounded to 1 decimal place and this value written to a cell. How can i round this value correctly.
View 4 Replies
View Related
Oct 22, 2009
I know about the 7 max nested function. But I cannot seem to use VLOOKUP to complete my task. Here is what i got.
=IF(E14=1,".05%",IF(E14=2,".10%",IF(E14=3,".15%",IF(E14=4,".20%",IF(E14=5,".25%",IF(E14=6,".30%",IF( E14=7,".35%",IF(E14=8,".40%"))))))))
This formula is set in a "list" validation. So if I click drop down and select "1" I get a return of .05% obviously as the formula shows......ok, we got it!
Problem: So now I want to add IF E14=0,".0%, but it will exceed my precious "7" IF function max? Can someone PLEASE help the rookie with this formula.
View 14 Replies
View Related
Jul 16, 2007
How can I make excel display 321.165 as 321.16 when I have the cell format set to number and 2 decimal places instead of rounding it up to 321.17?
View 11 Replies
View Related
Jun 24, 2008
custom number format.
The numbers in the worksheet read like this: 114'31.5
The number behind the hyphen are not to exceed 32, after 32 they become a full point (115'00.0).
View 12 Replies
View Related