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-unsubscr...@openoffice.apache.org > For additional commands, e-mail: dev-h...@openoffice.apache.org > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org > For additional commands, e-mail: dev-h...@openoffice.apache.org >
Spreadsheet-bug_FJCC.ods
Description: application/vnd.oasis.opendocument.spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org For additional commands, e-mail: dev-h...@openoffice.apache.org
