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

Author Message
airtaz
Newbie

Joined: 22 Nov 2006
Posts: 4

 Posted: Wed Nov 22, 2006 3:44 pm    Post subject: Help with a way to work with 2 cells 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Nov 22, 2006 4:56 pm    Post subject: 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
airtaz
Newbie

Joined: 22 Nov 2006
Posts: 4

Posted: Wed Nov 22, 2006 5:14 pm    Post subject:

 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 ?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Wed Nov 22, 2006 5:29 pm    Post subject: An empty cell evaluates to zero and any string is greater than any number. A1
airtaz
Newbie

Joined: 22 Nov 2006
Posts: 4

Posted: Wed Nov 22, 2006 5:50 pm    Post subject:

 Villeroy wrote: An empty cell evaluates to zero and any string is greater than any number. A1

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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Wed Nov 22, 2006 6:12 pm    Post subject:

 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
airtaz
Newbie

Joined: 22 Nov 2006
Posts: 4

 Posted: Wed Nov 22, 2006 8:11 pm    Post subject: 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.
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

 Posted: Wed Nov 22, 2006 9:24 pm    Post subject: 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
Nylo
General User

Joined: 16 Nov 2006
Posts: 17

 Posted: Wed Nov 22, 2006 10:12 pm    Post subject: 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Thu Nov 23, 2006 5:46 am    Post subject:

 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
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

Posted: Thu Nov 23, 2006 2:16 pm    Post subject:

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 )

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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Thu Nov 23, 2006 3:35 pm    Post subject:

 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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