openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "F C. Costero" <>
Subject Re: creating a spreadsheet (was: new cool feature)
Date Thu, 16 Aug 2018 16:14:37 GMT
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.


On Wed, Aug 15, 2018 at 11: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
> 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:
> For additional commands, e-mail:
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:

View raw message