[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 6:08 am    Post subject: [SOLVED]Count numbers that shows up in pairs. Hi, I need a formula to count how many times a number in a row shows up as a pair (neighbouring cells) Look at this example COUNT 2 IN A ROW 4 blank blank 4 blank blank 4 4 blank blank 4 4 blank RESULT= 2 In this example, the result for count would be 2, as number 4 turns up 2 times in a row 2 times) The count would NOT include numbers who turns up MORE than TWO times, I´d have a specific formula for that event. a formula to COUNT how many times a number turns up THREE times in a row, and a specific formula that counts how many times a number turns up FOUR times in a row and so on.... COUNT 3 IN A ROW blank blank 4 4 4 blank RESULT = 1 In this example, the result for count would be 1, as number 4 turns up 3 times in a row 1 time) Thanks for any advice and have a nice day!!Last edited by fivefootnine on Sun Apr 22, 2012 10:55 am; edited 1 time in total
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK

Posted: Sun Apr 22, 2012 6:42 am    Post subject:

 Code: =INDEX(FREQUENCY(FREQUENCY(IF(A1:A13=4,ROW(A1:A13)),IF(A1:A13<>4,ROW(A1:A13))),{1,2}),2)

and
 Code: =INDEX(FREQUENCY(FREQUENCY(IF(A1:A13=4,ROW(A1:A13)),IF(A1:A13<>4,ROW(A1:A13))),{2,3}),2)

See:
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 7:14 am    Post subject: thanks thank you, anyway, this gives me the ERORR:508 message, even when confirmed with ctrl+shift+enter, ?? waht could be causing that?
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK

 Posted: Sun Apr 22, 2012 7:38 am    Post subject: Try replacing the commas with semicolons._________________LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 9:33 am    Post subject: I tried to replace the commas with semicolon and the ERROR message disappeared. Instead the number 14 came up, when I changed the (A1:A13) to (A1:A12) the number 13 came up instead,, confused...
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK

 Posted: Sun Apr 22, 2012 9:50 am    Post subject: It works for me: _________________LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 9:52 am    Post subject: Thanks, I think I need to download the english version of CALC, I have the swedish version, and I might have translated the code wrong,,, thank you so much!!
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK

Posted: Sun Apr 22, 2012 9:59 am    Post subject:

The Swedish is:

_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 10:54 am    Post subject: Thanks a lot!! It works now!! AWESOME!! PROBLEM solved!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum