I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).
I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".
I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.
I have attached a sample of the offending spreadsheet. The rounding function is in column "B"
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
I am trying to write a formula that will round numbers to $.05, $1, $5, and $10. The formula needs to be written in a way that If c75 <100 round to $.05, if c75 is greater than $101 but less than $500, round to $1, if c75 is greater than $501 but less than $1,000, round to $5, and if c75 is greater than $1,001, round to $10.
=IF(C75<100,ROUND(C75*20,0)/20)
but i can't quite figure out how to get the rest of it to work.
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8 Cell D2 contains a pre-calculated number (e.g. 53.012) Cell F2 is the cell that I need an IF function or something similar – the function would be as follows =IF(B2=1,D2*1.000) =IF(B2=2,D2*1.00057) =IF(B2=3,D2*1.00171) =IF(B2=4,D2*1.002281) =IF(B2=5,D2*1.003421) =IF(B2=6,D2*1.003991) =IF(B2=7,D2*1.004215) =IF(B2=8,D2*1.004538)
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel: -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(-1)*7 -6--7 -6+7=1
But in VBA you get -6-(|-6/7|)*7 -6-(|-0.857142857|)*7 -6-(0)*7 -6-0=-6
If you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:
Sub roundingtest() Dim a As single Dim b As single Dim cases As Integer a = 18200 b = 0.01 cases = Int(a * b) Debug.Print cases; End Sub
this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?
Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so 182.1 -> 183 182.9 ->183 182 ->182
So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!
I have a column, where i want to calculate the difference between today and another date/or viceversa. The problem is, that in the first two cells, it calculates it well, but then, it shows #VALUE and when I press to see the "calculations steps" , it shows "29/09/2009"-40049. It turns the today function into a number. Why?
This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.
The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.
I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.
I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I have 3 conditions within my spreadsheet, which only 1 condition can have a numeric value in it at any one time.
The result of my calculations returns the following results Condition 1: False; Condition 2: 168001.5072; Condition 3: False I then use the MAX command to pick up the cell that has a value in it. The command I use is: =MAX(L28,N28,P28)
The problem occurs is when I use this MAX command it Rounds up the value from 168001.5072 to 168001.51 - I need to stop this rounding from occurring.
Now I have just been told on this forum about how to use the TRUNC command to stop my original formula from rounding my final value (168001.5072) but I'm not sure if using the TRUNC command will work in this situation.
I can not use the "=" command becuase depending on the values I use any 3 of the conditions can be met, which will mean a value will sit in either condition 1, 2 or 3, so that is why I need to pull the MAX value.
The attached image shows my issue.
The value on the right 168001.5072 is being rounded up and displayed as seen on the left as value 168001.51 and I would like ot know how to stop this from occuring.
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error
The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.
I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.
PHP Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double Dim I As Integer, Slope As Double, NData As Integer NData = UBound(X) For I = 1 To UBound(X) - 1 If (X(I) = X0) Then Interpolate = Y(I) Exit Function ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1)) Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1)) Exit Function End If Next I End FunctionPublic Function ListMax(ParamArray ListItems() As Variant) Dim I As Integer ListMax = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) > ListMax Then ListMax = ListItems(I) Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant) Dim I As Integer ListMin = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) < ListMin Then ListMin = ListItems(I) Next IEnd Function
I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.
Sub B_Test() Dim myRange Dim Results Dim Run As Long myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown)) Range("M3") = Application.WorksheetFunction.Sum(myRange) myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown)) Range("D3") = Application.WorksheetFunction.CountA(myRange) End Sub
I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.
I am importing a .txt file into Excel that has two columns of dollars and cents. When I do my subtotals, all subtotals should be zero. Some of the totals show as 0.00, some as (0.00) and some as a dash which is what I would like for all of them. I have tried: ...
I have the following formula: =TEXT(( SUM(D98,D117,D136))/12,"0")&"' - "&MOD((SUM(D98,D117,D136)),12)&""""
This formula adds the inches in D98, D117 & D136 and divides it by 12 to get the footage of the 3 cells. The problem occurs when you reach the half foot mark...for instance say D98 was 2-0 (24 inches), D117 was 3-0(36 inches) and D136 was 2-6(30 inches) added up this comes out to 7-6(90 inches)...the formula then rounds the footage up to 8 feet...this can understandably be frustrating when I'm trying to get an exact measurement...is there a way to force the formula not to round up?
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name Cell B - Monday Cell C - Tuesday Cell D - Wednesday etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
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)
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
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?
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?
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...
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.
some call databases from my internal telemarketing team, which are all on Excel databases. These contain multiple contacts within the same organisation, with no "unique identifier", i.e. there is no information specific to individual records EXCEPT their email address - and unfortunately, not every contact has an email address, which would prevent using VLOOKUP, which is the only function I can use to perform this type of lookup.
I have five separate spreadsheets, plus one master database spreadsheet. I have added five columns to the end of my master database, and I would like to use each column to identify which spreadsheet(s) the individual records appear in - normally this would be possible by using a VLOOKUP in each of the five new columns, selecting a unique identifier, and using the VLOOKUP function for each separate spreasheet. However, without a consistent unique identifier, I do not know another function which would allow me to use multiple identifying info (e.g. "FirstName" + "Surname" + "Company") to perform this task.
If SheetExists(Sheets("data").Range("V" & x).Value) Then 'do Nothing Else Sheets.Add ActiveSheet.Name = Sheets("Data").Range("V" & x).Value End If ProjectReview.Show
Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function
I'm trying to add a new sheet, if a sheet with that name doesn't exist already. Which I have a feeling is pretty simple!. My Error is occurring at the * in the code. Basically I am adding a sheet per record. I'm using a unique identifier for each tab. The first run through, it creates a new sheet and opens a user form. When I close out that form, it returns to here and fails to work the second time through. I'm fairly confident it's because I'm using a select statement, I've also tried using it this way, but the set statement is invalid because it's not refrencing an object....
I want to do a vlookup between two tabs on a date in cell A1 eg 01-Apr-08 The lookup needs to be on month and year so I'm using
=Month(A1) which gives an answer 4 in cell A2 =Year(A1) which gives an answer 2008 in cell B2 I then oncatenate the results (=A2&B2) to get a unique reference 12008 - works ok
On the second tab, I've repeated the formula, I get an answer of 1 and 2008 but when I concatenate, I get a #VALUE!
I've tried "text to columns" unchecked Protect on the cells worksheet is not protected
I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.
I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.
Now I close everything.
I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.
I have put together the following code and it works fine on my PC. However, we are a mainly Mac based company and I nead it to work with a Mac. I am using the Actual ODBC Driver for Mac to give me a ODBC connection. I can do the query in excel but setting it up as a macro just will not work.
Sub Sales_Query() Columns("C:D").Select Selection.Delete Shift:=xlToLeft Range("B2").Select Dim area As Variant area = Range("B2") With ActiveSheet.QueryTables.Add(Connection:= Array("ODBC;DSN=my_database;Description=My................................
