I have a table, let's call it Table1. Each column has the ability to filter but no filters are applied at the outset of this issue. I want to average a column labeled Column13. I also want to apply a filter to Column13 that states only show values over 50%. I apply the filter and in the cell I want the average calculated in I type the formula:
"=AVERAGE(101,Table1[Column13])"
The 101 (as far as I'm lead to believe) only applies the average to the rows *SHOWN*. However, the average I get back is 45.67%. This is obviously wrong as the filter states only show rows where the column is greater than 50%.
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
I'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.
I have this formula that returns a grade mark for a specific score in a test:
=VLOOKUP(D4,'Grade scale'!$A$34:$B$42,2)
using these values in the lookup table:
Low scoreGrade 0.0% U 25.0% G 35.0% F 45.3% E 55.3% D 65.5% C 72.0% B 78.0% A 85.0% A*
The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?
I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.
I am working on a form to pull employee identifiers such as employee ID, store number as well as sales performance. I am using the below formula but the data that is being returned is not the correct data for the specific employee. C5 is the employee name (last name, first name) in a single cell. A sample of the spreadsheet I am trying to pull data from is attached. Some employees the formula pulls the right data and some it does not...
I 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.
I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
What is required to delete the visible rows post filtering?
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=9, Criteria1:= _ "Other" 'insert code to delete visible rows ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select ActiveSheet.AutoFilter.Range.Offset(4).SpecialCells(xlCellTypeVisible).Select ActiveSheet.AutoFilterMode = False Selection.Delete Shift:=xlUp 'this is where the error is occuring. End Sub
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv) If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
I have a pivot table with multiple supervisors and emloyees, I need to be able to find the average of a supervisors department and compare it with an employee with the employee seeing everyone else's data.
I don't have access to the original data that drives this pivot table. Is there some way I can use a calculated field to contain the average performance of all the employees under this particular supervisor?
Is it possible to AVERAGE() the numbers that remain from a filter, and average only those numbers? Can the AVERAGE() change with the criteria that are filtered?
how to calculate a simple average after removing duplicates. The trick is the average must work after the spreadsheet is filtered. I was able to make it work with a VBA script but the users don't like this since the numbers don't automatically update whenever they apply a different filter.
What I need: average (mean) value of (in this case age in years) based on unique ID#s that doesn't include duplicates (some ID#s are in database several time):
ID# Age Filter Options
11111 27 Option A
11111 27 Option A
[code]....
So if I filter by Option C, the cell with the average should return the average age of only the filtered ID#s but without the duplicates.
Correct age w/no filter: 56.8 Correct age w/option C: 59
What I have tried using: (reason for difference in cells on the equation, I condensed it in the above example so A=C and B=G)
This array works but will return hidden and non-hidden cells b/c of average(if), so it doesn't work with filter:
{=AVERAGE(IF(C3:C13C4:C14, G3:G13,FALSE))}
Also have tried the Subtotal 101 function, also with no luck: Note, a simple subtotal101 fxn will not work b/c even when I filter there will still be a few rows that are duplicate and need to be removed before averaging
I am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is
=IF(C23:C30="","",AVERAGE(C23:C30))
I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range) Dim Roundname As String Dim Result As Double Dim NameColRound As Integer Dim ListObject As Variant
Roundname = "Round " & Round NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0) For Each ListObject In NameList If ListObject.Value = Name Then Result = Result ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then Result = Result + 1 End If Next maxbarometer = Result End Function
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
Is it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
When copying data from a website to excel, excel recognizes the dates wrongly.
About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.
For example: The cell contains the date: 01/04/2029 (The first of April, 2029)
While it should actually be: 04/29 (The 29th of April, no year!)
For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.
About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.
For example: The cell contains the text: June 12
When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.
I have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution. I'm using excel 2003.
Let me try to explain my problem with a simple exampel.
I have a column with numbers and one with formulas pointing at these. A B 2 =A1 4 =A2 3 =A3
Now I want to sort column A, and the column B should keep pointing at the original number. So I want this: A B 2 =A1 3 =A3 4 =A2
But I get this: A B 2 =A1 3 =A2 4 =A3
This output is what I would expect if I used absolut reference($).
Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.
I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.
My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.
I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in english?
