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

Help with a way to work with 2 cells

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


Joined: 22 Nov 2006
Posts: 4

PostPosted: Wed Nov 22, 2006 3:44 pm    Post subject: Help with a way to work with 2 cells Reply with quote

Thanks for help.

Basically what I need to do is evaluate if cell1 is <= cell2. If it is true, then change the value of cell2 to that of cell1. If cell2 is greater than cell1 then leave it the same. The only cell that will be changeable by the user is cell1.

After browsing these forums I thought iteration was the key. But it still isn't working.

I'm not against using a 3rd cell for the formula to manipulate cell2. But since the value of cell2 isn't going to be editable by the user, it always starts with a value of zero...making cell1 always greater than cell2.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Nov 22, 2006 4:56 pm    Post subject: Reply with quote

Use a third cell like this:
=IF(cell1<=cell2;cell1;cell2)
This returns what you need in cell3 without touching the other cells.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
airtaz
Newbie
Newbie


Joined: 22 Nov 2006
Posts: 4

PostPosted: Wed Nov 22, 2006 5:14 pm    Post subject: Reply with quote

Villeroy wrote:
Use a third cell like this:
=IF(cell1<=cell2;cell1;cell2)
This returns what you need in cell3 without touching the other cells.


Yeah...tried that. That is changing the third cells value...which I don't really want that to be shown. Also this brings up a new situation as well as to the existing problem. The first time the user uses the spreadsheet, the cell2 will be empty (nil), making cell1 never <= cell2. Any ideas on some if nil check ?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Nov 22, 2006 5:29 pm    Post subject: Reply with quote

An empty cell evaluates to zero and any string is greater than any number.
A1<B1 is TRUE if B1 is empty and A1<0
=IF(OR(ISBLANK(B1);A1<=B1);A1;B1)
Try Ctrl+F2
OR belongs to category "Logical" ISBLANK belongs to category "Information".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
airtaz
Newbie
Newbie


Joined: 22 Nov 2006
Posts: 4

PostPosted: Wed Nov 22, 2006 5:50 pm    Post subject: Reply with quote

Villeroy wrote:
An empty cell evaluates to zero and any string is greater than any number.
A1<B1 is TRUE if B1 is empty and A1<0
=IF(OR(ISBLANK(B1);A1<=B1);A1;B1)
Try Ctrl+F2
OR belongs to category "Logical" ISBLANK belongs to category "Information".


I appreciate any help I can get. However I'm not sure why you said the same thing three times. I am new to spreadsheets, especially OOO....not programming methods. This is why I expected there to be some sort of way to run a if nil... then...type of check. This can be programmed in 2 minutes tops. Unfortunately that isn't an option for this. I am required to use a spreadsheet.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Nov 22, 2006 6:12 pm    Post subject: Reply with quote

Quote:
I appreciate any help I can get. However I'm not sure why you said the same thing three times.

I answered two times. First answer was about using a third cell as a result. I could not know that you already tried, but don't like it.
Second answer was about how to check for empty cells. I gave you an example and added the information that an empty cell is not necessarily lower than another one if the other one is <0.
Quote:
I am new to spreadsheets, especially OOO....not programming methods.

In fact every spreadsheet is a programming language, although very limited. You have to know about the types of input/output when using cell-functions.
Quote:
Unfortunately that isn't an option for this. I am required to use a spreadsheet.

If you are required to use a spreadsheet, then use it according to it's specifications.
Quote:
This can be programmed in 2 minutes tops.

If you want to write spreadsheet-macros without knowing the application, go ahead: There are two excellent forae on this site:
http://www.oooforum.org/forum/viewforum.phtml?f=9
http://www.oooforum.org/forum/viewforum.phtml?f=11
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
airtaz
Newbie
Newbie


Joined: 22 Nov 2006
Posts: 4

PostPosted: Wed Nov 22, 2006 8:11 pm    Post subject: Reply with quote

It seems my interpretation from your initial response was well...misinterpreted. I apologize.

I don't think the logic I'm trying to use is wrong. I haven't really made it that far into it with the spreadsheet. My main issue at the moment seems to be controlling (or assigning) what's in a cell using information from another cell. I tried the 3 cell method. The formula changes the cell the formula it is in, not the intended cell. I'll try and explain 'exactly' what I am trying to accomplish.

When the user starts the spreadsheet, cells A1 and A2 are both empty. He will enter a number in A1. Eventually, A2 will be updated if A1 is a smaller number. The isblank function works fine for setting A2 to zero at the start. For the very first time he enters a number in A1, that number will have to be put in A2. After this, it will be based on whether or not it's smaller than A2.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Wed Nov 22, 2006 9:24 pm    Post subject: Reply with quote

airtaz,

You are trying to get the spreadsheet to do something which it is not designed for. Cells can contain data or formulae. You cannot put a formula in a cell to update data in the same cell. This requires a macro, and then you have to consider how you invoke the macro.

Another possibility is to have the cell linked to a control on a form. You might be able to assign a default value that way (speaking from ignorance here: I don't play with forms).

It looks to me like a data entry form for a database might do what you are after. But this is not an option given you are constrained to use a spreadsheet.

A bit more background on your application might help us suggest more appropriate alternatives (no guarantees).
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
Nylo
General User
General User


Joined: 16 Nov 2006
Posts: 17

PostPosted: Wed Nov 22, 2006 10:12 pm    Post subject: Reply with quote

No need to use a macro, there's a much easier solution you may have not noticed.

I will call "Formula2" to the Formula you have now written in Cell2.

Substitue it by next formula: =MAX(Formula2;Cell1)

This will show the result of Formula2 only if it is bigger than the value in Cell1, and the value of Cell1 otherwise.

Editted: After reading again, it looks obvious that you need the MAX function, not the MIN function.

CU.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Nov 23, 2006 5:46 am    Post subject: Reply with quote

airtaz wrote:
... Eventually, A2 will be updated if A1 is a smaller number...

No, an empty cell won't be updated. If both cells are empty initially: Smaller than which number?
A cell has a constant value, a formula or it it's left empty. This is how things are since the days of Visicalc on Apple ][ and every new concept I've seen on the net, reinforces strict separation of data from calculation in order to enhance reliability and clarity of spreadsheets.
An empty cell remains empty until *you* change it (input, paste, calling some advanced method or macro).

Start with a most simple design. Put all constant values into a dedicated range, using reasonable test values.
The first interesting (slightly advanced) step at this point may utilize scenarios, which allow you switching between different sets of test-data.

Then start writing your calculation model (set of formulas) in a separate range.
The first issue at this point is the logic of absolute and relative references. This is a common _and_most_important_ concept of all popular spreadsheets. Mastering this simple concept makes you a wizzard.
Split all formulas into most tiny steps. This makes things much easier in the long run.
For better overview you can split/freeze windows and you can hide columns and rows.
I would even split up Nylo's =MAX(<calculation>;A1) into:
B1: =<calculation>
C1: =MAX(B1;A1)
For instance you may want to reuse <calculation> later or you need to track down the exact step where an unexpected value occurs.

Go back to your input range of constant values and test some insane values (text instead number and vice versa, missing values). There are several ways to handle this in a given calculation model.

The final step should involve nice formatting, moving/hiding/protection of cells, so the user gets a clean picture of input and resulting output on screen and printer.
Notice: Formatted numbers may be very irritating when designing a model since some number formats cover the real value of a cell.

Dale wrote:
Another possibility is to have the cell linked to a control on a form. You might be able to assign a default value that way (speaking from ignorance here: I don't play with forms).

Nope, default values are handled by a database engine, linked to your form control. A sheet does not care about defaults nor uniqueness nor validity(not really*). Each cell is supposed to take any number or string, calculated or not.

*)Data>Validation allows validation of input to some degree but nothing prevents a user from clearing, removing or pasting over validated cells.
Tools>Protection>Sheet protects locked cells from beeing edited, but it is easy to remove the lock.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Thu Nov 23, 2006 2:16 pm    Post subject: Reply with quote

Villeroy wrote:
Dale wrote:
Another possibility is to have the cell linked to a control on a form. You might be able to assign a default value that way (speaking from ignorance here: I don't play with forms).

Nope, default values are handled by a database engine, linked to your form control.
(Very) bad assumption on my part. Now that I've actually had a look I see that there is no (built-in) way to set a default value on a control. (Note to self: check first, post later Embarassed )

Thanks for clarifying.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Nov 23, 2006 3:35 pm    Post subject: Reply with quote

Dale wrote:
(Very) bad assumption on my part.

No, not that bad. Me too expected the control's default value to be set when the form is loaded with the document. But a linked cell seems to have a higher precedence. A linked empty cell sets a listbox to null (no selection) and a numeric field shows zero, even though both have default values.
Defaults of unbound controls (not bound to a database) can be implemented through very simple macros:
Code:

Sub myForm_OnLoad(oEv)
  oEv.Source.getByName("NumField").setValue(23)
End Sub
'Same with first cell on first sheet:
Sub myDoc_OnLoad()
thisComponent.Sheets(0).getCellByPosition(0,0).setValue(23)
' or by name:
thisComponent.Sheets.getbyName("Sheet1").getCellByName("A1").setValue(23)
End Sub

If Sheet1.A1 is linked with control "NumField" any one of the 3 lines will set both values. I prefer simple templates with preset values as named refs or placed on a hidden setup-sheet. No macro-warnings and full functionality with Excel and Gnumeric.
airtaz wants to catch the modification of a specific cell and set some value then. This is possible, but not trivial. It is against the general concept of the application. Someone may add this kind of gimmick when the real spreadsheet-stuff is done, but still I can not see any reason to do so.

airtaz wrote:
It seems my interpretation from your initial response was well...misinterpreted. I apologize.

No offence taken. My interpretation was more like: "Hey, this guy tries very hard swimming against the work flow!". This often leads to great innovations, but when you start using a fairly complex and powerful tool you propably end up with a bloody nose.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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