You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Donald Town <bg...@sbcglobal.net> on 2023/11/02 07:55:21 UTC
Formula construction help
Hi everyone
I'm working on a database that has a group of basic formulas to get needed values.
I'd like to configure a "Yes / No' location (set of adjacent cells) to identify if a value is greater than / less than a boundary.
If > 14 then report "No".
If < or = 10 then report "Yes".
My only issue is not knowing how to construct the formula correctly.
Any help appreciated.
Thanks
Don
Using a couple different versions of OO on 3 different older Macs.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by Donald Town <bg...@sbcglobal.net>.
On Saturday, November 4, 2023 at 11:10:28 PM EDT, David Robley <da...@robley.au> wrote:
Responses in line:
>On 2023-11-05 11:58, Donald Town wrote:
>>
>> First, thanks to ALL of you for your responses & courtesy.
>>
>> Second, I don't know how to tell / determine what kind of document it is... between database or ... "Calc'... whatever distinguishes.
>At the top left of the window in which you are working should appear the
filename followed by the application that has opened the file - Calc for
spreadsheet or Base for database.
Ah! D'oh! I MISSED that staff meeting! Yup, it's there but I never noticed that part!
>> Mathematically I could / should have written it as...
>>
>> If K102 < 14 then L102 = "Yes", else L102 = "No"
>I'll continue to assume Calc until proven otherwise.
No longer an assumption now!
>Note that a spreadsheet formula can only affect the cell in which it is contained.
THIS is why it wasn't working for me. I was trying to do it by "remote control" from elsewhere!
>If you want to display the "Yes" or "No" in L102 then the formula must be in L102 and would be:
>=IF(K102 < 14; "Yes"; "No")
I made a test file on THIS computer and it worked.
Transferred the test file to the OTHER machine and it also worked.
Somehow I was making one other minor mistake ... after I had tried it in the correct spot.
>> I can set up a simplified version if that helps everything... and if it's ok to send it to the list.
>The list strips attachments, so that wouldn't be possible.
Good thing to know. Kind'a thought that was the case...
I've actually been monitoring the list a little for some time now... but hadn't sent anything.
MOST times I can figure out most of what is needed.
>Alternatively
>you could register and post on
>https://forum.openoffice.org/en/forum/index.php
Hmmm... I did register quite some time ago.
Actually sending thie message, I grabbed the address to post straight off someone else's post.
My issue HERE may be that I'm using a different computer than the one I registered on.
THAT one... is down and not functional... so maybe the mailing software is blocking me from posting directly because I'm not posting from the origin machine that I originally subscribed from... a borrowed computer.
> So, that's where things are...and most certainly I do appreciate the input / feedback / help.
> Thanks again for everyone's input and courtesy.
... and Robin, please don't give up on being courteous to people.
Your on list courtesy in your attempt is certainly *NON-toxic", & friendly!
I appreciate your olive branch!
> Don
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by David Robley <da...@robley.au>.
Responses in line:
On 2023-11-05 11:58, Donald Town wrote:
>
> First, thanks to ALL of you for your responses & courtesy.
>
> Second, I don't know how to tell / determine what kind of document it is... between database or ... "Calc'... whatever distinguishes.
At the top left of the window in which you are working should appear the
filename followed by the application that has opened the file - Calc for
spreadsheet or Base for database.
> Third, from the Get Info box... on one ( Macbook ) computer, I'm using ...
> Version: OpenOffice 4.1.8 AOO418m3 (Build:9803)
>
> ...and on a different computer ( a more often used iMac)
> Version: 3.1.1 (Build: 9420)
>
> The formula I should have used works logically elsewhere outside OO, but not in the OO document.
> When asking originally, I errored using the above as a two part formula... that actually contained a problem in the first place.
> Apologies for the mistaken way I described my predicament originally.
> Mathematically I could / should have written it as...
>
> If K102 < 14 then L102 = "Yes", else L102 = "No"
I'll continue to assume Calc until proven otherwise. Note that a
spreadsheet formula can only affect the cell in which it is contained.
If you want to display the "Yes" or "No" in L102 then the formula must
be in L102 and would be:
=IF(K102 < 14; "Yes"; "No")
>
> ... but I most certainly still do not understand how to make that work right in my document.
> I can set up a simplified version if that helps everything... and if it's ok to send it to the list.
The list strips attachments, so that wouldn't be possible. Alternatively
you could register and post on
https://forum.openoffice.org/en/forum/index.php
>
> So, that's where things are...and most certainly I do appreciate the input / feedback / help.
> Thanks again for everyone's input and courtesy.
>
> Don
> On Saturday, November 4, 2023 at 08:00:25 PM EDT, 許哲崇 <fi...@gmail.com> wrote:
>
> We may have common interest.
> So, please keep me informed.
> I am learning LaTex and equations in word. You may want to review my
> Facebook 許哲崇 (Fideric Hsu)
>
> Donald Town <bg...@sbcglobal.net> 於 2023年11月2日 週四 下午3:55 寫道：
>
>> Hi everyone
>>
>>
>> I'm working on a database that has a group of basic formulas to get needed values.
>>
>> I'd like to configure a "Yes / No' location (set of adjacent cells)
>> to identify if a value is greater than / less than a boundary.
>> If > 14 then report "No".
>> If < or = 10 then report "Yes".
>>
>> My only issue is not knowing how to construct the formula correctly.
>> Any help appreciated.
>>
>> Thanks
>> Don
>>
>> Using a couple different versions of OO on 3 different older Macs.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by Donald Town <bg...@sbcglobal.net>.
First, thanks to ALL of you for your responses & courtesy.
Second, I don't know how to tell / determine what kind of document it is... between database or ... "Calc'... whatever distinguishes.
Third, from the Get Info box... on one ( Macbook ) computer, I'm using ...
Version: OpenOffice 4.1.8 AOO418m3 (Build:9803)
...and on a different computer ( a more often used iMac)
Version: 3.1.1 (Build: 9420)
The formula I should have used works logically elsewhere outside OO, but not in the OO document.
When asking originally, I errored using the above as a two part formula... that actually contained a problem in the first place.
Apologies for the mistaken way I described my predicament originally.
Mathematically I could / should have written it as...
If K102 < 14 then L102 = "Yes", else L102 = "No"
... but I most certainly still do not understand how to make that work right in my document.
I can set up a simplified version if that helps everything... and if it's ok to send it to the list.
So, that's where things are...and most certainly I do appreciate the input / feedback / help.
Thanks again for everyone's input and courtesy.
Don
On Saturday, November 4, 2023 at 08:00:25 PM EDT, 許哲崇 <fi...@gmail.com> wrote:
We may have common interest.
So, please keep me informed.
I am learning LaTex and equations in word. You may want to review my
Facebook 許哲崇 (Fideric Hsu)
Donald Town <bg...@sbcglobal.net> 於 2023年11月2日 週四 下午3:55 寫道：
> Hi everyone
>
>
> I'm working on a database that has a group of basic formulas to get needed values.
>
> I'd like to configure a "Yes / No' location (set of adjacent cells)
> to identify if a value is greater than / less than a boundary.
> If > 14 then report "No".
> If < or = 10 then report "Yes".
>
> My only issue is not knowing how to construct the formula correctly.
> Any help appreciated.
>
> Thanks
> Don
>
> Using a couple different versions of OO on 3 different older Macs.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
Re: Formula construction help
Posted by 許哲崇 <fi...@gmail.com>.
We may have common interest.
So, please keep me informed.
I am learning LaTex and equations in word. You may want to review my
Facebook 許哲崇 (Fideric Hsu)
Donald Town <bg...@sbcglobal.net> 於 2023年11月2日 週四 下午3:55 寫道：
> Hi everyone
>
>
> I'm working on a database that has a group of basic formulas to get needed values.
>
> I'd like to configure a "Yes / No' location (set of adjacent cells)
> to identify if a value is greater than / less than a boundary.
> If > 14 then report "No".
> If < or = 10 then report "Yes".
>
> My only issue is not knowing how to construct the formula correctly.
> Any help appreciated.
>
> Thanks
> Don
>
> Using a couple different versions of OO on 3 different older Macs.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
Re: Formula construction help
Posted by Robin Lord <ro...@fastmail.fm>.
I do apologise it must have been sql washer I used it, Howartb if that's your attitude it's the last time I will try to help
On Fri, 3 Nov 2023, at 14:36, Brian Barker wrote:
> On 2023-11-02 22:06, Robin Lord wrote:
>>The IIF function will do what you want easily
>
> On Thu, 2 Nov 2023, at 21:55, David Robley wrote:
>>Unfortunately IIF isn't implemented in AOO Calc.
>
> At 14:12 03/11/2023 +0000, Robin Lord wrote:
>>I think it is I have used it in the past, of is aoo different from oo
>
> What you "think" is not helpful to others. It takes only seconds to
> confirm that OpenOffice does not have an IIF() spreadsheet function -
> which you could have done.
>
> Brian Barker
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
--
Best Regards
Robin
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by Brian Barker <b....@btinternet.com.INVALID>.
On 2023-11-02 22:06, Robin Lord wrote:
>The IIF function will do what you want easily
On Thu, 2 Nov 2023, at 21:55, David Robley wrote:
>Unfortunately IIF isn't implemented in AOO Calc.
At 14:12 03/11/2023 +0000, Robin Lord wrote:
>I think it is I have used it in the past, of is aoo different from oo
What you "think" is not helpful to others. It takes only seconds to
confirm that OpenOffice does not have an IIF() spreadsheet function -
which you could have done.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by Robin Lord <ro...@fastmail.fm>.
I think it is I have used it in the past, of is aoo different from oo
On Thu, 2 Nov 2023, at 21:55, David Robley wrote:
> Unfortunately IIF isn't implemented in AOO Calc.
>
> On 2023-11-02 22:06, Robin Lord wrote:
>> The IIF function will do what you want easily
>>
>> On Thu, 2 Nov 2023, at 09:57, David Robley wrote:
>>> On 2023-11-02 18:25, Donald Town wrote:
>>>> Hi everyone
>>>>
>>>> I'm working on a database that has a group of basic formulas to get needed values.
>>>>
>>>> I'd like to configure a "Yes / No' location (set of adjacent cells) to identify if a value is greater than / less than a boundary.
>>>> If > 14 then report "No".
>>>> If < or = 10 then report "Yes".
>>>>
>>>> My only issue is not knowing how to construct the formula correctly.
>>>> Any help appreciated.
>>>>
>>>> Thanks
>>>> Don
>>>>
>>>> Using a couple different versions of OO on 3 different older Macs.
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>>>> For additional commands, e-mail: users-help@openoffice.apache.org
>>>>
>>> You haven't said which component of AOO you are working with. You
>>> mention database but then mention adjacent cells, which would suggest Calc.
>>>
>>> Assuming the latter, a formula like the following should achieve what
>>> you seem to want. You haven't mentions what to display if value is
>>> between 10 and 14 so I've used UNDEFINED; replace with whatever you
>>> want. Assuming your "value" is in A1, place in B1
>>>
>>> =IF( A1 > 14; "NO"; IF( A1 <= 10; "YES" ; "UNDEFINED"))
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>>> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
--
Best Regards
Robin
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by David Robley <da...@robley.au>.
Unfortunately IIF isn't implemented in AOO Calc.
On 2023-11-02 22:06, Robin Lord wrote:
> The IIF function will do what you want easily
>
> On Thu, 2 Nov 2023, at 09:57, David Robley wrote:
>> On 2023-11-02 18:25, Donald Town wrote:
>>> Hi everyone
>>>
>>> I'm working on a database that has a group of basic formulas to get needed values.
>>>
>>> I'd like to configure a "Yes / No' location (set of adjacent cells) to identify if a value is greater than / less than a boundary.
>>> If > 14 then report "No".
>>> If < or = 10 then report "Yes".
>>>
>>> My only issue is not knowing how to construct the formula correctly.
>>> Any help appreciated.
>>>
>>> Thanks
>>> Don
>>>
>>> Using a couple different versions of OO on 3 different older Macs.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>>> For additional commands, e-mail: users-help@openoffice.apache.org
>>>
>> You haven't said which component of AOO you are working with. You
>> mention database but then mention adjacent cells, which would suggest Calc.
>>
>> Assuming the latter, a formula like the following should achieve what
>> you seem to want. You haven't mentions what to display if value is
>> between 10 and 14 so I've used UNDEFINED; replace with whatever you
>> want. Assuming your "value" is in A1, place in B1
>>
>> =IF( A1 > 14; "NO"; IF( A1 <= 10; "YES" ; "UNDEFINED"))
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by Robin Lord <ro...@fastmail.fm>.
The IIF function will do what you want easily
On Thu, 2 Nov 2023, at 09:57, David Robley wrote:
> On 2023-11-02 18:25, Donald Town wrote:
>> Hi everyone
>>
>> I'm working on a database that has a group of basic formulas to get needed values.
>>
>> I'd like to configure a "Yes / No' location (set of adjacent cells) to identify if a value is greater than / less than a boundary.
>> If > 14 then report "No".
>> If < or = 10 then report "Yes".
>>
>> My only issue is not knowing how to construct the formula correctly.
>> Any help appreciated.
>>
>> Thanks
>> Don
>>
>> Using a couple different versions of OO on 3 different older Macs.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
>>
> You haven't said which component of AOO you are working with. You
> mention database but then mention adjacent cells, which would suggest Calc.
>
> Assuming the latter, a formula like the following should achieve what
> you seem to want. You haven't mentions what to display if value is
> between 10 and 14 so I've used UNDEFINED; replace with whatever you
> want. Assuming your "value" is in A1, place in B1
>
> =IF( A1 > 14; "NO"; IF( A1 <= 10; "YES" ; "UNDEFINED"))
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
--
Best Regards
Robin
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formula construction help
Posted by David Robley <da...@robley.au>.
On 2023-11-02 18:25, Donald Town wrote:
> Hi everyone
>
> I'm working on a database that has a group of basic formulas to get needed values.
>
> I'd like to configure a "Yes / No' location (set of adjacent cells) to identify if a value is greater than / less than a boundary.
> If > 14 then report "No".
> If < or = 10 then report "Yes".
>
> My only issue is not knowing how to construct the formula correctly.
> Any help appreciated.
>
> Thanks
> Don
>
> Using a couple different versions of OO on 3 different older Macs.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
You haven't said which component of AOO you are working with. You
mention database but then mention adjacent cells, which would suggest Calc.
Assuming the latter, a formula like the following should achieve what
you seem to want. You haven't mentions what to display if value is
between 10 and 14 so I've used UNDEFINED; replace with whatever you
want. Assuming your "value" is in A1, place in B1
=IF( A1 > 14; "NO"; IF( A1 <= 10; "YES" ; "UNDEFINED"))
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org