OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]Count numbers that shows up in pairs.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 22, 2012 6:08 am    Post subject: [SOLVED]Count numbers that shows up in pairs. Reply with quote

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
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Sun Apr 22, 2012 6:42 am    Post subject: Reply with quote

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:
http://www.ozgrid.com/forum/showthread.php?t=71645
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 22, 2012 7:14 am    Post subject: thanks Reply with quote

thank you, anyway, this gives me the ERORR:508 message, even when confirmed with ctrl+shift+enter, ?? waht could be causing that?
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Sun Apr 22, 2012 7:38 am    Post subject: Reply with quote

Try replacing the commas with semicolons.
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 22, 2012 9:33 am    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Sun Apr 22, 2012 9:50 am    Post subject: Reply with quote

It works for me:


_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 22, 2012 9:52 am    Post subject: Reply with quote

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!!
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Sun Apr 22, 2012 9:59 am    Post subject: Reply with quote

The Swedish is:

Code:
=INDEX(FREKVENS(FREKVENS(OM(A1:A13=4;RAD(A1:A13));OM(A1:A13<>4;RAD(A1:A13)));{1;2});2)

_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
fivefootnine
Power User
Power User


Joined: 09 Apr 2012
Posts: 60

PostPosted: Sun Apr 22, 2012 10:54 am    Post subject: Reply with quote

Thanks a lot!!
It works now!!
AWESOME!! PROBLEM solved!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group