Ask Your Question

New Libre user needs assistance [closed]

asked 2016-07-25 17:33:39 +0100

Weasel gravatar image

updated 2017-12-10 17:15:10 +0100

erAck gravatar image

I get the feeling this is going to be a little long winded so, please bare with me... I have a column which has hours in it, which is totalled at the bottom, for anything that has more than 2:29 in it, it has a break associated with it, which would be found from the Breaks page, but rather than having each individual cell showing a break, i wondered if there is a way, to take each of the hours from each cell, calculate each break, and give me a total in the M/R cell. I am sorry if I am a little vague, but if you need more info, I will try and make things a little more clear. Hours Breaks

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Weasel
close date 2016-07-27 17:29:23.179441

1 Answer

Sort by » oldest newest most voted

answered 2016-07-25 23:31:54 +0100

Lupp gravatar image

updated 2016-07-27 13:49:18 +0100

If I understood correctly you best should use VLOOKUP to get the breaks associated with the durations.
Using OFFSET you can parametrise the sizes of the tables. Adding the breaks under array evaluation (using SUM with Ctrl+Shift+Enter or SUMPRODUCT) you can get the total of breaks in one single cell. I would surely prefer to dedicate a helper column for the breaks.
See attached.

(Editing with respect to the second comment by @Weasel":)
Filling rows with data (durations in this case) is a kind of keeping data. Generally professionals advise to use database software for the purpose then. Insisting on the usage of spreadsheets, we should obey some basic principles of databasing nonetheless, disallowing unused rows among them.
Thus: Enter/create zeros where zero duration is a fact. If you don't want to get these zeros displayed, use a specialised 'Numbers' format or a conditional format. Avoid to show nothing in place of a zero value. You can also display a hint ("-/-" e.g.) without ruining the value 0 which might be needed for evaluations. To get counted only non-zero values you may use COUNTIF then...

edit flag offensive delete link more


Thanks for the info, sorry for my lateness in replying... has been busy day, and am now trying to make it work in the current layout i have...

Weasel gravatar imageWeasel ( 2016-07-26 23:04:20 +0100 )edit

Ok... Nearly there, but have encountered another problem...
link text unless I fill the empty cells with zero, I am getting errors...
Should I open a 2nd ticket or just wait here?

Weasel gravatar imageWeasel ( 2016-07-27 01:20:08 +0100 )edit

@Weasel: You should generally avoid empty (or blank or unused) cells inside a data coillection.
LOOKUP functions (VLOOKUP or combinations of MATCH with INDEX/OFFSET e.g.) can produce different errors if applied with blank/empty/unused cells in the to-match-position or inside the find-here-ranges as well. Detailed advice concerning possible remedy requires access to the actual document.
Also: Many columns of same structure without a clear distinction are unadvisable.

Lupp gravatar imageLupp ( 2016-07-27 13:38:14 +0100 )edit

Yes, I see your point noted
Was trying to make life simpler for work, and save a little time, but looks like I'll have to stick with the old way, I'll mark this up as answered, as the info is good, and whoever has the power to, can close this. Thanks again, has been fun.
(Edit due to new info) turns out I can close it, so will do so now

Weasel gravatar imageWeasel ( 2016-07-27 17:25:35 +0100 )edit

Question Tools

1 follower


Asked: 2016-07-25 17:33:39 +0100

Seen: 77 times

Last updated: Jul 27 '16