A couple questions... [solved]

Hoser117 · General User Joined: 31 Jan 2012 Posts: 7

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 Cool

, 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!

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

Hoser117 · General User Joined: 31 Jan 2012 Posts: 7

Thank you! And yeah, I had imagined the second question would be rather complicated to do.

floris_v · 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 · 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).

ozzie · 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.