New cool feature

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

New cool feature

Archie Dyno Wizard
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";"PE301AN";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: [hidden email]
For additional commands, e-mail: [hidden email]

Spreadsheet-bug.ods (16K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: creating a spreadsheet (was: new cool feature)

F C. Costero
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 <[hidden email]> 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: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Spreadsheet-bug_FJCC.ods (26K) Download Attachment