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

A couple questions... [solved]

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


Joined: 31 Jan 2012
Posts: 7

PostPosted: Wed Feb 01, 2012 8:23 pm    Post subject: A couple questions... [solved] Reply with quote

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 8Cool, 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
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Feb 01, 2012 9:48 pm    Post subject: Reply with quote

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


A1:A600 being the range adjust as required.

Q2
Can't help you there!
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Hoser117
General User
General User


Joined: 31 Jan 2012
Posts: 7

PostPosted: Thu Feb 02, 2012 6:23 am    Post subject: Reply with quote

Thank you! And yeah, I had imagined the second question would be rather complicated to do.
Back to top
View user's profile Send private message
floris_v
Moderator
Moderator


Joined: 12 Jul 2007
Posts: 4791
Location: Netherlands

PostPosted: Thu Feb 02, 2012 6:51 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Thu Feb 02, 2012 7:05 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu Feb 02, 2012 6:25 pm    Post subject: Reply with quote

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.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
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