Hi,
I’ve been developing a spreadsheet given by a colleague that allows me to track annual leave, time off in lieu etc. The spreadsheet has been broken down to either note full day, half day am and half day pm.
I have 5 categories where someone could be absent either for the full day or either half and as such the spreadsheet records it this way. All the categories are located on column OH, these are referenced in summation calculation.
My issue is when it comes to summing days absent it is not doing so correctly when I enter a half day code. In cell NK8 it sums all leave taken in the year. I have “F” in cells B8 & C8 for fixed leave on 1st and 2nd January. if I enter “A” in cell D8 this then sums to 3 in cell NK8 which is correct. However if I change “A” to “A1” to represent absent for half day only, it changes the value in NK8 to 1.5 rather than 2.5.
I think it’s a silly error where I am applying the 0.5 factor to the total rather than just the limited cells but can’t see where to fix it. The formula I am using is this:
Any help would be appreciated.
Thanks,
EMcK
I’ve been developing a spreadsheet given by a colleague that allows me to track annual leave, time off in lieu etc. The spreadsheet has been broken down to either note full day, half day am and half day pm.
I have 5 categories where someone could be absent either for the full day or either half and as such the spreadsheet records it this way. All the categories are located on column OH, these are referenced in summation calculation.
My issue is when it comes to summing days absent it is not doing so correctly when I enter a half day code. In cell NK8 it sums all leave taken in the year. I have “F” in cells B8 & C8 for fixed leave on 1st and 2nd January. if I enter “A” in cell D8 this then sums to 3 in cell NK8 which is correct. However if I change “A” to “A1” to represent absent for half day only, it changes the value in NK8 to 1.5 rather than 2.5.
I think it’s a silly error where I am applying the 0.5 factor to the total rather than just the limited cells but can’t see where to fix it. The formula I am using is this:
Code:
{=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(IF(OR(OFFSET($A8,0,1,1,372)=$OH$9,(OFFSET($A8,0,1,1,372)=$OH$10),(OFFSET($A8,0,1,1,372)=$OH$13),(OFFSET($A8,0,1,1,372)=$OH$14),(OFFSET($A8,0,1,1,372)=$OH$16),(OFFSET($A8,0,1,1,372)=$OH$17),(OFFSET($A8,0,1,1,372)=$OH$19),(OFFSET($A8,0,1,1,372)=$OH$20),(OFFSET($A8,0,1,1,372)=$OH$22),(OFFSET($A8,0,1,1,372)=$OH$23)),0.5,IF(OR(OFFSET($A8,0,1,1,372)=$OH$9,(OFFSET($A8,0,1,1,372)=$OH$10),(OFFSET($A8,0,1,1,372)=$OH$13),(OFFSET($A8,0,1,1,372)=$OH$14),(OFFSET($A8,0,1,1,372)=$OH$16),(OFFSET($A8,0,1,1,372)=$OH$17),(OFFSET($A8,0,1,1,372)=$OH$19),(OFFSET($A8,0,1,1,372)=$OH$20),(OFFSET($A8,0,1,1,372)=$OH$22),(OFFSET($A8,0,1,1,372)=$OH$23)),0.5,1)*(OFFSET($A$3,0,1,1,372)))))}Any help would be appreciated.
Thanks,
EMcK
