| View previous topic :: View next topic |
| Author |
Message |
fivefootnine
Power User
Joined: 09 Apr 2012
Posts: 60
|
 Posted: Sat Jun 16, 2012 11:07 am Post subject: [SOLVED]count the cells between two specified numbers |
 |
|
Hi, I need to know how to write a formula that counts the cells between two specified numbers in a Column, for example, In Column A, I´d like to count the cells between number 99 and 8, please look at the example below.
A
99
5
6
2
3
1
8
The formula would give me the answer : 5 (as there are 5 cells between number 99 and 8 in Column A)
thanks and have a great day!
Last edited by fivefootnine on Sat Jun 16, 2012 11:14 pm; edited 1 time in total |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Sat Jun 16, 2012 11:19 am Post subject: |
|
|
count() but you would have to know the cells not just the values
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
fivefootnine
Power User
Joined: 09 Apr 2012
Posts: 60
|
| Posted: Sat Jun 16, 2012 11:47 am Post subject: |
|
|
thanks for the fast reply, what I want to do is to count how many cells there is between A1(which may contain a frozen number, like 99) and a given number, let´s say 8. I would never know where the number 8 would appear,, it could appear in A2 (that would make the answer 0), or A100(that would make the answer 99) or anywhere between..
thanks |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia
|
| Posted: Sat Jun 16, 2012 2:40 pm Post subject: |
|
|
If the numbers are in A1:A100 then try...
| Code: | | =MATCH(8;A1:A100;0)-MATCH(99;A1:A100;0)-1 |
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
