Hi there,
I have a column with several different time specifications. Now I want to create a formula, that evaluates every single time specification in this column, group it afterwards and calculate the number of each time specification. Is there I chance how I can fix this problem?
Thanks you for your help
Sflany
.
So ... for example if you had 20 entries in Col A (A1:A20) consisting of time entries ... you want the formula to return the number 20 say in B1 ?
If this is your goal look at the excel formula for COUNTA
http://https://support.office.com/en...2-53f3219e2509
thank you for your answer, but that is not what I want to do
I have a column with different times for example 12:00:00, 09:00:00, 12:15:00 and so on (see image).
example.JPG
In a first step I would like to find out every single time information given in this column. In a second step I would like to calculate the number of each time.
.
Perhaps it's just me but your request is still confusing.
Please explain in different words what this means :
find out every single time information given in this column
Does this :
mean you want to know how many of the time entries are (say) 12 noon ? And how many might be 5 pm, etc ?like to calculate the number of each time.
Attach a sample workbook (not image).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Attached you can find my data:
I try to explain what I want to do.
In the yellow column you can find all data I have. In a next step I would like to get a list where there are only these times listed which can be found in the yellow column. In my example data (column D) I listed every single minute of a day, but I just want to know the times listed in column A.
Then I would like to calculate how often every single time can be found (see therefor the green column / which should be the final result).
Last edited by sflany; 10-30-2017 at 10:29 AM.
My first thought was to use a pivot table. If you are unfamiliar with Excel's pivot tables, this should get you started: http://www.excel-easy.com/data-analy...ot-tables.html I chose to use time_data as the row labels and count of time_data as the values in the pivot table.
Originally Posted by shg
Thanks for your help MrShorty - it worked perfectly with a pivot table!
It seems you'd like to create an unique distinct list of times in column A in D column, then count.
I will try to create a A-Z order list. Try as follow:
In D2:
=MIN($A$2:$A$825)
D3:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Please Login or Register to view this content.
Drag down till you get "0:00:00"
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks