I'm using Excel 2010. I have a range of data like:
1 1 1 0 1 0 1 0 0 1 0 0 0 1 0
I need to count the number of "isolated" zeroes in this range of data. In this example the answer would be "3". What is the best way to approach this?
As per the attached sheet Count If is not operating.
Effective Reading Capability
Effective Reading Capability
Effecting Writing Capability
Application of Basic Mathematics 0.4
Effective Listening Skills 0.3
Effective Speaking Capability 0.3
Attitude to Cleanliness 0.3
Idea Generation & Creative thinking 0.1
Decision Making Skills ( Upto the mandate)0.2
[code].....
here If I say Countif(B4:B20, >=0.5) But it shows Zero.
I have a spreadsheet with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.
View 6 Replies View RelatedI have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx
I have data arranged in a worksheet (see attachment) that has hours of work broken down by day. What I need is a formula that will find the number of times a record occurred in Column F that is greater than or equal to 12 hours each day. So for March 1st there would be 9 times. I can do that now with no problem using "=COUNTIF(F4:F14,">=12")" However, the real thing that I need is how many days of each month were there only 1 count (of 12 hours or more). So it needs to look at the range of data that goes from 3/1/13 to 3/31/13 and find the total number of days that had 1 count (of 12 hours or more) each day and return the number of days it found.
View 6 Replies View RelatedI have a simple pivot table that is types by state. I want to know how many types are in each state. In the attached image the answer for Alaska is 5, for Arizona 5, for Arkansas 1, etc. How do I get Excel to tell me that for each state?
I'm using Excel 2010. I have PowerPivot installed but really don't know how to use it yet.
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
I am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
I have data set up like the example but then for 1797 lines.
D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo
[code].....
What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.
How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010
Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".
Category
Types
No of Types
Fruits
Apple
Pear
Orange
3
Vegetable
Cabbage
1
Excel 2010
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.
If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
I have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
How this can be done?
I am currently using Excel 2010. How to Count Singles, Doubles, Triples and Quadruples in a group of number.
Frankie
0
1
2
9
Count how many single numbers (numbers all different). Example: 0129
7
8
8
8
4
8
3
6
[Code] .......
Count how many double numbers (two numbers the same in a draw). Example: 8830
9
2
6
2
8
3
5
4
[Code] ......
Count how many triple numbers (three numbers the same in a draw). Example: 7888
8
6
1
2
9
5
9
6
[Code] ......
Count how many quadruple numbers (all numbers the same in a draw). Example: 2222
6
4
5
1
5
1
0
2
[Code] .........
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
I have a column of dates in Col H with associated values in Col I. I need to specify a date range in Cell I1 and I2 such that the row number for the first encounter of the first date is placed in Cell I3 and the last encounter of the end date is placed in Cell I4. For example, with this data when I specificy 4/2/13 and 4/3/13, I would like to get a 19 in Cell I3 and a 53 in Cell I4.
Matty supplied the formula in Col K for each of these cells. They worked well in the application that I supplied earlier (different locations for these variables), but my real application is as shown here, and these two formula give the incorrect results shown. Both of these formula are arrays.
Excel 2010HIJK14/2/201324/3/2013310=MATCH(I1,INT(H10:H5000),0)+1444=MATCH(2,1/(INT(H11:H5000)=I2),1)+1536789X10 114/1/13 1:366.97124/1/13 2:04134/1/13 2:04144/1/13 4:563.95154/1/13 4:573.27164/1/13 5:165.55174/1/13 5:172.35184/1/13 10:30194/2/13 14:00204/2/13 14:59214/2/13 15:01224/2/13 17:192.81234/2/13 17:191.59244/2/13 17:252.14254/2/13 17:262.05264/2/13 21:07274/2/13 21:07284/2/13 21:11294/2/13 21:11304/3/13 1:38314/3/13 1:38324/3/13 2:10334/3/13 2:10344/3/13 4:24354/3/13 5:152.84364/3/13 5:154.11374/3/13 5:173.45384/3/13 5:173.24394/3/13 9:35404/3/13 9:35414/3/13 9:59424/3/13 10:01434/3/13 13:36444/3/13 13:37454/3/13 13:41464/3/13 13:42474/3/13 17:124.03484/3/13 17:133.62494/3/13 17:15504/3/13 17:15514/3/13 21:12524/3/13 21:13534/3/13 22:214.41544/4/13 1:52554/4/13 1:52564/4/13 1:53574/4/13 1:53Ppk Raw Data (2)
I am trying to get the average of the last 6 data entries not including blanks or zeroes.
Currently I have:
{=AVERAGE(INDEX(C2:CP2,LARGE(IF(C2:CP2<>"",COLUMN(C2:CP2)-COLUMN(C2)+1),6)):CP2)}
Which works fine when I've got 6 or more values in the row.
But if there are less that 6 values in the row it returns with #NUM!. Is there a way to add an IF ERROR THEN make = to AVERAGE of C2:CP2?
Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.
View 3 Replies View RelatedI am trying to insert a Pivot table with dynamic Name Ranges. It needs to start from Cell B1 as Column A has hidden formulas in them. I created an Dynamic Name range and tried to insert a pivot table. Excel then throws out a error stating "Data source reference is not valid". I tried re-saving the document but still no luck.
View 1 Replies View RelatedI am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:
"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.
Sheets("Data Forwards").Select
ActiveSheet.Range("$A$1:$U$1000").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$1000"), , xlYes).Name = _
"Table1"
ActiveSheet.Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
I can see that the range is highlighted in the sheet before the code breaks.
Excel 2010. I have a macro, stored in a workbook.
The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
Run on Excel 2010
Sub UseChangePassword() Dim wksOne As Worksheet Set wksOne = Application.ActiveSheet
' Protect the worksheet. wksOne.Protect ' Establish a range that can allow edits ' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _ Title:="Classified", _ Range:=Range("A1:A4"),
_ Password:="secret" MsgBox "Cells A1 to A4 can be edited on the protected worksheet."
' Change the password. wksOne.Protection.AllowEditRanges(1).ChangePassword
_ Password:="moresecret" MsgBox "The password for these cells has been changed." End Sub
The red color code is giving error message: Application defined or Object defined error.
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
I have to report Actual Headcount every month. Since I cannot add the Headcount numbers in the FY Column. What is the best possible forumula I can use to pick the last month's reported numbers into the FY Column.
For example.
I have 13 columns (A - M). Jan - Dec and the 13th column is the FY number.
This month, I am reporting March numbers in Column C
Column D through N (Apr through Dec) are emplty
I need Column N (FY number) to read Column C and enter the value from that cell.
similarly when I report April numbers in Column D next month, I want Column N to read April numbers and ignore the prior months.
I tried using the min/max function but since h/c fluctuate every month I could not use that. I could use an "if" statement and go back every column but that seems to be inefficient way to solving for this. Any thoughts?
BTW, I am using Excel 2010
There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .
I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.
Excel 2010
A
B
C
D
[Code]...