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.
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.
ask73923SumValuesLookedUp001.ods
(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…
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…
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: 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.
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