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

Author Message
Hoser117
General User

Joined: 31 Jan 2012
Posts: 7

 Posted: Wed Feb 01, 2012 8:23 pm    Post subject: A couple questions... [solved] First one is rather simple, how do I count the number of 0's in a column where each cell is either a 0 or a 1? Second I think is rather complicated.... Suppose I have a section of a row, 5 cells long, with 5 numbers. 97 85 88 98 88 Now then, I'd like to have the ability to overwrite the last number (the 8, by just say typing over it with 90, and then having all the numbers shift over one, but delete the 97. So I have.... 97 85 88 98 88 I overwrite the 88 with 90, and now I have 85 88 98 88 90 All the numbers have shifted to the left once, and the left most number has been deleted. Is it possible to do this? I'd imagine this is a rather complicated request, but maybe not!Last edited by Hoser117 on Thu Feb 02, 2012 6:47 am; edited 1 time in total
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Wed Feb 01, 2012 9:48 pm    Post subject:

Q1
 Code: =COUNTIF(A1:A600;0)

A1:A600 being the range adjust as required.

Q2
_________________
Hoser117
General User

Joined: 31 Jan 2012
Posts: 7

 Posted: Thu Feb 02, 2012 6:23 am    Post subject: Thank you! And yeah, I had imagined the second question would be rather complicated to do.
floris_v
Moderator

Joined: 12 Jul 2007
Posts: 4791
Location: Netherlands

 Posted: Thu Feb 02, 2012 6:51 am    Post subject: You cannot do # 2 in the way you describe - it'd mean that every time you want to overwrite a cell value, that cell and all cells to the left of it would shift to the left. You will have to add a value to the right, then delete the left most cell._________________LibreOffice 3.6.3; OOo 3.4.1 on Windows Vista Join the Official community forum - in several languages, including Nederlandstalig forum
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

 Posted: Thu Feb 02, 2012 7:05 am    Post subject: Your second request is tricky because you are wanting to retrieve an overwritten cell value. It might be possible using macro code but I personally don't know how. The macro code is fairly simple provided you're prepared to accept a small change in the sheet's set up. Instead of overwriting the 5th cell, use a 6th cell as an input cell. The code can compare the 5th and 6th cells, and when they are different numbers, the values in the 2nd, 3rd, 4th and 5th cells would be moved to the 1st, 2nd, 3rd and 4th cells then the 5th cells value can be changed to the value in the input cell (6th cell). The macro code can be triggered by the sheet's "Content changed" event (See "Sheet Events..." in the menu that appears when you right-click the sheet tab). See the attached doc (Shift on change.ods) where F1 is the input cell for the five values in A1:E1. When F1 is changed to a number that is not the same as the number in E1, B1:E1 values are moved to A1:D1 then E1 is changed to F1's value. E1 is locked and the sheet is protected (no password) to prevent the user from accidentally changing E1 instead of the input cell F1, which would trigger the macro code after the E1 value has been overwritten and hence lost. http://www.mediafire.com/view/?44kr21c6yjd9qdk Ken Johnson_________________If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).Last edited by ken johnson on Thu Sep 27, 2012 2:33 am; edited 1 time in total
ozzie

Joined: 29 Jul 2010
Posts: 400
Location: victoria

Posted: Thu Feb 02, 2012 6:25 pm    Post subject:

Hi Hoser117

Reading the timing of postings, and your speed in adding solved to the title makes me wonder if you have been misled by my reply (I only meant that 'I' was unable to do what you asked of Q2) as macro's are outside of my comfort zone.

Your second posted question makes me wonder if these are perhaps the last 5 scores in which you are tracking wins and loses. As such I have have an alternative that will not do what you asked but will give the appearance of such and may at least be of interest to you.

Try this out on a new spreadsheet. Copy paste the below formula into cell A2 and drag it (using the black square at the bottom right of the active cell) across to E2.

 Code: =INDEX(\$G\$1:\$G\$32;COUNT(\$G\$1:\$G\$32)-5+COLUMNS(\$A\$1:A1))

Then just start filling in numbers in the G column starting from G1.
_________________