I have the following equation programed in Excel, (see file attached). I do not understand while the match funstion is not working the lookup value is the on in cell N1 the array is from B1:F1. The return value should be 3 but I get #NA instead at the begining I was thinking that the values were different but then I compared cell =D1=N1 and the result was TRUE, meaning that they are equal
View 4 Repliesi have converted my text from notepad into excel format and i realise that some of my columns are shift 1 cell away for example my applications name should be in cell A but after converting, some of my cell A is move into Cell B. is there anyway that i can delete the cell that are blank and shift to Cell A or shift everything from cell b into cell a if cell a is blank? i have alot of documents to change..
View 10 Replies View RelatedI have a spreadsheet, with a Match Formula, that appears not to work. It looks at cell F1, and scans through cell C2:C14, and want it to return the relevant row in Column B (My work network will not allow me to upload the file). Cell F1 is the name of the sheets within the workbook (one for every month). However my data currently only has Apr - Jun, but when I choose month May, the formula feturns Month June.
View 3 Replies View RelatedI'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View RelatedI 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
I have a function that starts as follows:
Function Timings(StartTime As Double, EndTime As Double, Times As Range, Calc As Long)
StartTime will refer to a cell in Column A that holds a date and time EndTime will refer to a cell in Column B that holds a date and time Times is the range of cells that hold times of the day I am testing against. The cells are within a range defined called Hours. Calc refers to different criteria that I am testing again. This is a number list between 1 to 5.
The function works well if the cell that StartTime or EndTime refer to cell values that are date/time. If the data is anything different, I get a #VALUE error such as if the cell contents is text. I tried to trap the error to see if the data inputted in the cell is not date/time and thus cannot be converted to a Double but it always throughs up #VALUE and the function seems as though it fails when it . Is there anyway to trap the error of wrong data type in the first the first few lines of code in the function?
Example
Column A Column B Column C Formula
05/01/2013 12:00 06/01/2013 09:00 =Timings(A1,B1,Hours, 1)
25/02/2013 15:00 30/02/2013 14:30 =Timings(A2,B2,Hours, 1)
03/01/2013 16:00 abc =Timings(A3,B3,Hours, 1)
Row 2 will return #VALUE becuase 30/02/2013 14:30 is an invalid date (30th Feb does not exist so Excel seems to pickup as text)
Row 3 will return #VALUE because it is text and cannot be converted to a double data type
The attached workbook contains a cell with validation list selection and a look up function. The validation list selection works fine, but the look up function is coming up with the wrong results on several items. Is this a bug or am I doing something wrong?
View 2 Replies View RelatedI have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies View RelatedUsing the text to columns option on a comma separated file in csv format leads to the right preview in the text to columns wizard.
The column titled "ATTIC: Zone ..." shows the desired format in the preview window. Please look at the screenshot 1.jpg.
After pushing the finish button to obtain the result the number format gets suddenly changed and differs from the preview.
Again check for the "ATTIC: Zone ..." column as reference: 2.jpg
Is there a menu where one can look up, or specify how to format data to force the right comma placement? What settings might be wrong?
Is there a function that returns the position (numberical order) of a cell within an array?
View 9 Replies View RelatedI am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedBasically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
I have attached a file called Test2 which has a public function called DBLookup. The function looks in the attached access database simulating a Vlookup just like in Excel. The problem I am having is when that data appears on sheet1. I am getting #VALUE in the cell and the erroe states "a used value in the formula is of the wrong data type".
my
Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "C:Test2.mdb"
'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return...
Another interesting dilemma to solve. Using this formula:
View 2 Replies View Relatedlet me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedIs there an easy to use macro or VBA function to delete the rows with "position" column value of a zero? I have both positive and negative values in the column. I tried to put on filter but am not sure how to make it a rule based function: i.e. show only < 0 <.
Date Type NamePosition
2006/09/20CRGJacob11167
2006/09/20CRGTom 0
2006/09/20CRGMark 58371
2006/09/20CRGSue0
2006/09/20CRGDawn0
I'm trying to use match function to find the max value that is less than a given value. look at my file. I think it should return F as the value, why is it returning G?
View 11 Replies View Related=MATCH(9.999999E+307,B12:Y12)-1
=MATCH(9.999999E+307,B18:Y18)-1
So what is this pulling and how does it change depending on the data?
I'm using the match function to tell me in which column the first positive/non-zero number appears. However, the formula is returning the # of columns in the range. Here is what I'm using:
=MATCH(1,H6:CA6,TRUE)
I'm getting 72 back as a result, which is the number of columns between column H and CA.
Any ideas why this is the case?
I have the below code sent in by a MR Excel forum user
Public Function BigMatch(rngIndexColumn As Range, varMatchValue As Variant, rngMatchRange As Range) As Variant
Dim lngCol As Long
Dim rngCell As Range
lngCol = rngIndexColumn.Column
For Each rngCell In rngMatchRange
If rngCell.Value = varMatchValue Then
BigMatch = Cells(rngCell.Row, lngCol).Value
End If
Next rngCell
End Function
when using the BigMatch Function = BigMatch(any cell in the column you want to pull from, the value you want to match, where you want to find that value)
I am using 2 different sheets, Technology & Lessons learnt: when I try the BigMatch Function
=BigMatch(Technology!C17,'Lessons Learnt'!A46,Technology!W17:AD27)
Instead of returning 'Technology'!C17 its returning the value from Lessons Learnt C17,
I have a very long function I created. But I need to edit it in a matter I have never done before.
Below is my function and i will like to add a new condition to it.
IF the text value in cell C7 is equal to any text value in Workbook2 B10:K10 and the value underneath that cell (I.e if B10 then B11 etc..) is Deleted then I want to have one of the conditions of my function below display "Deleted".
=+IF(AND(E7<=0,J7<>0,Y7<=0,AO7<=30),"Check Inventory",IF(AND(E7<=0,J7<>0,Y7<=0,AO7>=60),"Delete Listing ",""))
Using the address function to find the exact cell of a unique string text.
I'm using the following command to find the address of a string i have in a different cell.
=ADDRESS(MATCH("*$H$1*",A:A,0),1)
The string text is in cell H1, H2, H3.......(or on another sheet). The goal is to find the address of a cell containing the unique string text from cell H1, H2, H3.....(that entire column) in a sheet with 13,000 lines. Basically, searching for a specific cell address of a string text referenced from another cell.
=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)))
what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.
MATCH("b",{"a","b","c","b"},0) returns 2
how about if I would like to have the return result as 4 since I have another "b" within that same array?
is there anyones know what's the meaning of 1 in this formula
MATCH(1,('Data Issues'!$A2=Sheet1!$A$2:$A$68)*('Data Issues'!B2=Sheet1!$B$2:$B$68),0)
In Sheet 2 i have a 1000 of data contains the birth date of following customers
The result i want in Sheet 1 is
particular on todays (Say on 27-11-2008) date how many customer are having birthday, supoose there 10, or 8 wahtever should show me the list.
I tried this formula
=INDEX(Sheet2!$B:B,MATCH($B$3,Sheet2!$H:$H,0))
but by this formula it only show one customers birthdate what i want if there 10 differenrt customer those same birthdate it should display all the 10 date and name in diff rows
I'm trying to create a formula that would return the value of the column next to it. Looking at the below example, in a new cell I would want to be able to return the value 7 based on the fact that it is in category b.
I would think an if/offset/match function would do this but I can't seem to figure it out as it may require nesting.
categoryvalue
a1
a2
a3
a4
a5
a6
b7
I have one column full of duplicate names, column A. I have another 2 columns's E & F which contain the unique number and corresponding name to that number. I want to create a formula which i can put in column B which will match every name in A to the name in F but will enter the value as the unique number.
ie
A1 will contain Joe. E1 = 1 F1 =Joe. When I enter the formula in B1 it will show as 1. How would I write this as a formula?