|Yep I knew it would do someones head in, thought I would give it a go explaining this way in the hopes it might make sense! Apologies, I shall try again.|
(Also thanks for the link!!)
Fortnight 1AB 2AB 3AB 4AB 5AB 6AB
Quick Clean 2232 1843 2329 2169 0 0
Standard Clean 3103 3631 2964 3643 0 0
Full Cleans 72 59 29 0 0 0
Totem Poles 1297 1483 1407 1473 0 0
In the Full Clean Row, you can see that under 4AB the quantity of Full Cleans completed for that week is zero. There is a formula in the column after 6AB (not in the above table) where I've asked for excel to Average the numbers in all Full Cleans but ignore the zeroes (so the average will work)
The problem with this is that I now need the zero under 4AB to be included as part of the average formula, but the only way I can think of achieving this is to create a formula where the criteria could be that if there is a number above 0 in the Totem Poles row under 4AB, that it would no longer ignore the zero in the above cell (in this case, Full cleans under 4AB).
Please let me know if this is still confusing, I will be happy to try and explain it a bit better if this is the case!
Any help is much appreciated :)
message edited by annettesa