Excel 2007/10
The formula very nearly does what I need but I don't understand it well enough to adapt it.
Count the last three occurrences
My sheet looks like this;
A
B
C
D
E
F
G
[code].....
I modified this;
=IF(COUNTIF(B$2:B2,B2)
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 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 am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.
I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".
For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.
The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.
There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.
formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7
View 2 Replies View RelatedIn a datafile I have one column containing a trip 'origin' and a second column contains 'destinations'. I want to count how many times each trip occurs (so the same origin/destination pair). This is doable using COUNTIFS but unfortunately respondents did not provide consequent origins and destinations. I encountered the following formulations
-Alfastraat, Amsterdam
-Alfastraat 5, Amsterdam
-Alfastraat 5, 1021AB Amsterdam
-Amsterdam, Alfastraat
Since the format is different among and within respondents functions containing LEFT, RIGHT, MID are not useful (at least, my trials did fail). I found a VBA-script for a FUZZYVLOOKUP which sounded promising. Unfortunately the data is stored on a remote PC without VBA on it.
Is there a way to count the occurrences of trips given the circumstances?
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.
I would like the formulas to start in column B-D (data in A)
I am looking at formulas based on specific instances of a quote mark in cell from data in column A
Formula 1 - Return with characters up to and including the 5th instance of """
Formula 2 - Return with characters after 5th """ and up to and including 6th """
Formula 3 - Return with characters after 6th """ and up to and including 7th """
Formula 4 - Return with characters after the 7th instance of """
Example below...
Cell A1
xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"
Desired results
Cell B1 - Formula 1
xxyy","aabb","ee,ff,"
Cell C1 - Formula 2
"gghh"
Cell D1 - Formula 3
hh,"
Cell E1 - Formula 4
mmnbijp"oossww"
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedI ma using exel 2007 and I tryed to use this codes,I need faster to finish this project
Soo I have done only 2 buttons OK and Close the tab works like this I CLICK "Kerko" and then I write the name that I want to search in my Phonelist and when I click Ok it shows me his surname his telephone name and his City prephix
But the next and previous button I cant make them work,soo when I have to click next the name down the name that I first wroted will apear with his surname telepphone name and city prphix,and when I click prevoious will happend the same think but will not apear the next persone but the previous.
Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.
Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1
I'm creating an excel document that tracks the amount of time someone has (in months) in the program. Certain residents are able to 'fast track' if they meet conditions, and I am trying to create this spreadsheet so that anyone who looks at it can tell who qualifies (and when).
The issue I'm having is with conditional formatting, because I don't know how to do it with mutliple conditions.
Column C is their previous time (months) in the program
Column D is their current time (months) in the program
Column E is their total time in the program (Sum C+D)
Coumn E is what I would like to format, based on the following rules set out in the program manual:
If previous time is 9 months or more, a resident is eligible to fast track after 6 months current time (format green).
If previous time is less than 6 months, a resident is eligible to fast track after 9 months current time (format green).
I understand that their is an odd gap - but these are the rules currently set out by the program manual, which is what I have to follow.
I'm using Excel 2007 - if that changes anything.
Windows 7, Excel 2007. a formula to find the next lowest sequential number within a group.
Here's an example data set, which represents transactions by date and category:
Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)Col B is a user entered CategoryCol C is a formula that gives the chronological sort order based on the date.
The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1Col D is a formula to determine if the row is the first chronological transaction within the Category
The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")
This might not be a necessary column, but can be used as a helperCol E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).
I have entered in the values that the formula should return in Col E in the table below
A
B
C
D
E
[Code]...
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
E3 would return 4, since the previous transaction for Category A was line 9
C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowestE4 would return 6, since the previous transaction for Category A was line 3 C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowestE5 would return 1, since it is the first transaction in Category BE6 would return 1, since the previous transaction for Category B was line 5 C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowestEtc.
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
I need a formula to automatically change the summary column according to the month we are in.
Ie:
Last Months Data
Nov
Dec
Jan
Feb
1
8
1
7
4
7
9
2
9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
keep getting "The formula you typed contains an error".
To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.
3. If we want his age in years and months, then we can use a formula like this one:
=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”
The output of this formula will be as follows:
40 years and 2 months.
I change A1 to G3 because that is where I the birth date.
I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.
The layout is similar to this :
H1, H2, H3, H4, H5, H6
A4, A3, dummy, Dummy, ~,
A4, A3, dummy, "Dummy ,Dummy", ,
A4, A3, dummy, Dummy, +,
A4, A3, dummy, Dummy, -,
A4, A3, dummy, Dummy, "(5 + 4) /4",
Consider that the formula (5 + 4) /4 has a CR and LF between (5+4) and /4
I've tried a few methods to import the files:
Reading the CSV file as a Text File a line at a time but with the CR a LF it get's all messed up
[Code] .....
Tried with Using OLEDB but the Sign + , - and ~ are read a Null
[Code] ....
And Finally tried wit th QueryTable but it does not recognize the Double Quote that precedes the comma so
"Dummy ,Dummy" it's seen as two column values instead of one.
[Code] .....
I am trying to use the code that can find the last used row in particular column.
Here is the code in module1
Code:
Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp > temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function
and code in the range
Code:
=LastRow(V12:V300)
My problem is, this code was working nicely but now it only shows ###.
PS I use MS office 2010
I have just upgraded Excel from 2003 to 2010 and cannot locate where I would convert a spreadsheet to a .pdf document.
View 2 Replies View RelatedI have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words.
A1 = word 1
A2 = def. 1
A3 = word 2
A4 = def. 2
A5 = word 3
A6 = def. 3
Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming.
I'm using Excel 2010.
Is there functionality in Excel 2010 to convert a cell that has last name, first name to first name last name? For instance:
Before: Mouse, MickeyAfter: Mickey Mouse
I'm trying to merge data from 2 sources, and the common data between the two is the name. However, it is formatted differently in each data source.
USING EXCEL 2010
I am looking for a formula that will provide the right most value in a row.
I need to get the row number of the next available row , and my column contains many blanks .
Code:
Selection.End(xlDown).Select
Selection.End(xlUp).Select
will not work, nor will
Code:
Blast = WorksheetFunction.CountA(Range("b:b"))
HTML Code:
Excel 2010ABCDE230/09/2012SC12274-1brine 30/9/1231/10/2012FR12275-1brine 1/10/1241/10/2012BQ12275-
1yc12275-159/10/2012FR12283-1brine 9/10/1269/10/2012SC12283-1yc12283-1710/10/2012BQ12284-
1brine 10/10/12815/10/2012brine 15/10/12916/10/2012FR12290-11016/10/2012BQ12290-11122/10/2012SC12296-
[Code]....
In column b the next available row would be 16 ( the first 30/10/2012 row ), if I use the counta system i would get 7, and the xldown / xlup system would need to depend on where I start, which is what I need to determine
When I veiw this all I get is an immensely huge list of HMTL.
I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.
The layout is similar to this :
H1, H2, H3, H4, H5, H6
A4, A3, dummy, Dummy, ~,
A4, A3, dummy, "Dummy ,Dummy", ,
[Code]....