You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openoffice.apache.org by Peter Kovacs <Pe...@Apache.org> on 2018/08/16 05:57:09 UTC
creating a spreadsheet (was: new cool feature)
Hi Archie,
Welcome to OpenOffice. I whish you a lot of fun with the Software.
There are lots of possible ways to make your live easier.
I would recommend to use our forums or users mailing list to ask
questions on ways what you want to do.
I think there are all the features you do expect, but it is made
differently then you might think.
I have quickly exchanged the Postcode and the Miles with a lookup
function, I would use. But there is an issue. Can someone else have a
look? I do not find what I did wrong.
I loop in users for better support. Sorry, got to go. I am late for work
... :S
HTH
Peter
On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
> Dear developers! I'm beginning my experience with OpenOffice Calc, and
> I'm finding a few ugly bugs. First I think I'm too stupid, but then I
> realize it is a bug that doesn't depend on my knowledge. I have made
> one bug report about calculating time consumption and using the result
> in a formula for next cell.. But this mail is not about that. I was
> trying to find how to make my spreadsheet to fill cells according to
> previous cell, and finally I've found, that there is no option for
> that, so I created a long formula based on "IF" logic task. So now
> when I type a Name in "LOCATION" cell, it automatically recognizes it,
> and fills following cells "MILES", "POSTCODE", and "PAYRATE". Makes it
> so much easier, but makes difficult creating and maintaining the
> formula. So for now my 3-customer formula looks like this:
> =IF(C371="global
> stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton
> cambridge";"CM11HE";"-")))
> And it is only beginning of my Self Employment.
>
> So my suggestion is to create an additional AutoFill form where user
> can make a list of related data in specific columns or rows to fill up
> multiple cells at the same time.
>
> Thank you very much for such a wonderful opportunity to use a free
> Office Sofware!!! You guys rock!!!
>
> Attaching a piece of my Spreadsheet that shows bug in calculation of
> a "TOTAL INC" column, and the idea about AutoFill...
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:dev-unsubscribe@openoffice.apache.org
> For additional commands, e-mail:dev-help@openoffice.apache.org
Re: creating a spreadsheet (was: new cool feature)
Posted by "F C. Costero" <fj...@gmail.com>.
I changed the LOOKUP() function in columns D and E to be a VLOOKUP() with
the fourth parameter set to 0 so the function looks for an exact match. The
LOOKUP() function requires that the lookup vector be ordered. Since column
A of the Database sheet is not ordered, incorrect results were returned.
I fixed the formulas in column I to have the form
=H4-G4 + (G4 >H4)
This accounts for cases where the end time (H4) has a smaller clock value
(e.g. 07:00) than the start time (e.g. 19:00) but is in the next day. Times
are stored in units of days. The time 07:00 has the value 7/24 and the time
19:00 has the value 19/24. You were subtracting
7/24 - 19/24 = -12/24 = -0.5
If a cell is formatted as time, -0.5 is displayed as12:00, but the value is
still -0.5.
I appended
+ (G4 > H4)
which returns FALSE (equal to zero) when H4 is larger than or equal to G4.
When G4 is larger than H4, it returns TRUE (equal to one) that gives
H4-G4 + (G4 >H4) = 7/24 - 19/24 + (19/24 > 7/24) = -0.5 + (TRUE) = -0.5 + 1
= 0.5
The 0.5, when formatted as time, is 12:00 and it is the desired answer.
This is not a bug. All spreadsheets treat times as fractions of a day.
As Peter says, it is better to get help on the forum or the user list.
Francis
On Wed, Aug 15, 2018 at 11:57 PM, Peter Kovacs <Pe...@apache.org> wrote:
> Hi Archie,
>
>
> Welcome to OpenOffice. I whish you a lot of fun with the Software.
>
> There are lots of possible ways to make your live easier.
>
> I would recommend to use our forums or users mailing list to ask questions
> on ways what you want to do.
>
> I think there are all the features you do expect, but it is made
> differently then you might think.
>
>
> I have quickly exchanged the Postcode and the Miles with a lookup
> function, I would use. But there is an issue. Can someone else have a look?
> I do not find what I did wrong.
>
>
> I loop in users for better support. Sorry, got to go. I am late for work
> ... :S
>
>
> HTH
>
> Peter
>
>
> On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
>
> Dear developers! I'm beginning my experience with OpenOffice Calc, and I'm
> finding a few ugly bugs. First I think I'm too stupid, but then I realize
> it is a bug that doesn't depend on my knowledge. I have made one bug report
> about calculating time consumption and using the result in a formula for
> next cell.. But this mail is not about that. I was trying to find how to
> make my spreadsheet to fill cells according to previous cell, and finally
> I've found, that there is no option for that, so I created a long formula
> based on "IF" logic task. So now when I type a Name in "LOCATION" cell, it
> automatically recognizes it, and fills following cells "MILES", "POSTCODE",
> and "PAYRATE". Makes it so much easier, but makes difficult creating
> and maintaining the formula. So for now my 3-customer formula looks like
> this:
> =IF(C371="global stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton
> cambridge";"CM11HE";"-")))
> And it is only beginning of my Self Employment.
>
> So my suggestion is to create an additional AutoFill form where user can
> make a list of related data in specific columns or rows to fill up multiple
> cells at the same time.
>
> Thank you very much for such a wonderful opportunity to use a free Office
> Sofware!!! You guys rock!!!
>
> Attaching a piece of my Spreadsheet that shows bug in calculation of a
> "TOTAL INC" column, and the idea about AutoFill...
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: dev-help@openoffice.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: dev-help@openoffice.apache.org
>
Re: creating a spreadsheet (was: new cool feature)
Posted by "F C. Costero" <fj...@gmail.com>.
I changed the LOOKUP() function in columns D and E to be a VLOOKUP() with
the fourth parameter set to 0 so the function looks for an exact match. The
LOOKUP() function requires that the lookup vector be ordered. Since column
A of the Database sheet is not ordered, incorrect results were returned.
I fixed the formulas in column I to have the form
=H4-G4 + (G4 >H4)
This accounts for cases where the end time (H4) has a smaller clock value
(e.g. 07:00) than the start time (e.g. 19:00) but is in the next day. Times
are stored in units of days. The time 07:00 has the value 7/24 and the time
19:00 has the value 19/24. You were subtracting
7/24 - 19/24 = -12/24 = -0.5
If a cell is formatted as time, -0.5 is displayed as12:00, but the value is
still -0.5.
I appended
+ (G4 > H4)
which returns FALSE (equal to zero) when H4 is larger than or equal to G4.
When G4 is larger than H4, it returns TRUE (equal to one) that gives
H4-G4 + (G4 >H4) = 7/24 - 19/24 + (19/24 > 7/24) = -0.5 + (TRUE) = -0.5 + 1
= 0.5
The 0.5, when formatted as time, is 12:00 and it is the desired answer.
This is not a bug. All spreadsheets treat times as fractions of a day.
As Peter says, it is better to get help on the forum or the user list.
Francis
On Wed, Aug 15, 2018 at 11:57 PM, Peter Kovacs <Pe...@apache.org> wrote:
> Hi Archie,
>
>
> Welcome to OpenOffice. I whish you a lot of fun with the Software.
>
> There are lots of possible ways to make your live easier.
>
> I would recommend to use our forums or users mailing list to ask questions
> on ways what you want to do.
>
> I think there are all the features you do expect, but it is made
> differently then you might think.
>
>
> I have quickly exchanged the Postcode and the Miles with a lookup
> function, I would use. But there is an issue. Can someone else have a look?
> I do not find what I did wrong.
>
>
> I loop in users for better support. Sorry, got to go. I am late for work
> ... :S
>
>
> HTH
>
> Peter
>
>
> On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
>
> Dear developers! I'm beginning my experience with OpenOffice Calc, and I'm
> finding a few ugly bugs. First I think I'm too stupid, but then I realize
> it is a bug that doesn't depend on my knowledge. I have made one bug report
> about calculating time consumption and using the result in a formula for
> next cell.. But this mail is not about that. I was trying to find how to
> make my spreadsheet to fill cells according to previous cell, and finally
> I've found, that there is no option for that, so I created a long formula
> based on "IF" logic task. So now when I type a Name in "LOCATION" cell, it
> automatically recognizes it, and fills following cells "MILES", "POSTCODE",
> and "PAYRATE". Makes it so much easier, but makes difficult creating
> and maintaining the formula. So for now my 3-customer formula looks like
> this:
> =IF(C371="global stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton
> cambridge";"CM11HE";"-")))
> And it is only beginning of my Self Employment.
>
> So my suggestion is to create an additional AutoFill form where user can
> make a list of related data in specific columns or rows to fill up multiple
> cells at the same time.
>
> Thank you very much for such a wonderful opportunity to use a free Office
> Sofware!!! You guys rock!!!
>
> Attaching a piece of my Spreadsheet that shows bug in calculation of a
> "TOTAL INC" column, and the idea about AutoFill...
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: dev-help@openoffice.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: dev-help@openoffice.apache.org
>
Re: creating a spreadsheet
Posted by Martin Groenescheij <ma...@groenescheij.com>.
First of all Cells can only be filled by User Input or by entering a
Formula in the Cell.
It is your task to select the formula that best fit your requirements.
I have attached a modified Calc file it use Named Ranges with can be
used in the Lookup table.
You need to read some books or wiki pages on spreadsheets.
What I see is that you use From Times which are Greater that the To
Times, which gives negative values
I can't believe that you pay customers if you work from 19:00 till 7:00
the next morning, so you should calculate From Date Time till To Date
Time values.
On 16/8/18 3:57 pm, Peter Kovacs wrote:
>
> Hi Archie,
>
>
> Welcome to OpenOffice. I whish you a lot of fun with the Software.
>
> There are lots of possible ways to make your live easier.
>
> I would recommend to use our forums or users mailing list to ask
> questions on ways what you want to do.
>
> I think there are all the features you do expect, but it is made
> differently then you might think.
>
>
> I have quickly exchanged the Postcode and the Miles with a lookup
> function, I would use. But there is an issue. Can someone else have a
> look? I do not find what I did wrong.
>
>
> I loop in users for better support. Sorry, got to go. I am late for
> work ... :S
>
>
> HTH
>
> Peter
>
>
> On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
>> Dear developers! I'm beginning my experience with OpenOffice Calc,
>> and I'm finding a few ugly bugs. First I think I'm too stupid, but
>> then I realize it is a bug that doesn't depend on my knowledge. I
>> have made one bug report about calculating time consumption and using
>> the result in a�formula for next cell.. But this mail is not about
>> that. I was trying to find how to make my spreadsheet to fill cells
>> according to previous cell, and finally I've found, that there is no
>> option for that, so I created a long formula based on "IF" logic
>> task. So now when I type a Name in "LOCATION" cell, it automatically
>> recognizes it, and fills following cells "MILES", "POSTCODE", and
>> "PAYRATE". Makes it so much easier, but makes difficult creating
>> and�maintaining the formula. So for now my 3-customer formula looks
>> like this:
>> =IF(C371="global
>> stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton
>> cambridge";"CM11HE";"-")))
>> And it is only beginning of my Self Employment.
>>
>> �So my suggestion is to create an additional AutoFill form where user
>> can make a list of related data in specific columns or rows to fill
>> up multiple cells at the same time.
>>
>> Thank you very much for such a wonderful opportunity to use a free
>> Office Sofware!!! You guys rock!!!
>>
>> Attaching a piece of my Spreadsheet that shows� bug in calculation of
>> a "TOTAL INC" column, and the idea about AutoFill...
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:dev-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail:dev-help@openoffice.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org