|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 24th, 2010, 07:45 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the frequency for every change in numbers
Dear Expert,
Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ......... etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2 
|
|
#2
April 24th, 2010, 08:57 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
Hi
With numbers in A2 and down, insert this formula in B2 and copy it down as required: =IF(A2A3,COUNTIF($A$2:A2,A2)-SUMPRODUCT(--($A1:A$2=A2),$B1:B$2),"") Regards, Per "Elton Law" skrev i meddelelsen ... Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
|
#3
April 24th, 2010, 08:59 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
Use the Subtotal feature.
http://www.ozgrid.com/Excel/subtotal.htm -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
|
#4
April 24th, 2010, 09:08 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
Thanks for reply.
But subtotal does not suit my need as I don't want to change the display of the spreadsheet. I take a tiny portion out of the spreadsheet only. Thanks "ozgrid.com" wrote: Use the Subtotal feature. http://www.ozgrid.com/Excel/subtotal.htm -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
|
#5
April 24th, 2010, 09:48 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
In B2 and copied down;
=IF(A2A3,SUMIF(A1:A2000,A2),"") -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Thanks for reply. But subtotal does not suit my need as I don't want to change the display of the spreadsheet. I take a tiny portion out of the spreadsheet only. Thanks "ozgrid.com" wrote: Use the Subtotal feature. http://www.ozgrid.com/Excel/subtotal.htm -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
|
#6
April 24th, 2010, 01:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
Assuming your number start in A2 and down.
In B2: =IF(A2=A3,"",COUNTIF($A$2:A2,A2)-SUMIF($A$1:A1,A2&"",$B$1:B1)) "Elton Law" wrote: Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
|
#7
April 24th, 2010, 03:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count the frequency for every change in numbers
Hi all,
When I tried 2 series below, only teethless mama made it. Thanks indeed. 5 6 6 4 4 3 3 3 1 1 2 3 3 3 3 4 4 4 5 4 4 5 5 4 4 4 3 6 6 6 7 7 7 1 1 "Teethless mama" wrote: Assuming your number start in A2 and down. In B2: =IF(A2=A3,"",COUNTIF($A$2:A2,A2)-SUMIF($A$1:A1,A2&"",$B$1:B1)) "Elton Law" wrote: Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ........ etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
