Forum Discussion
Figuring out a formula
Happy Holidays everyone. I'm just stuck on trying to get the correct calculation/formula. In the Attachment A week is defined as 12:00am on a Saturday to 11:59pm on a Friday · The schedu...
Assuming I am not wrong:
From the image and the visible formula, I see no consideration of time or week numbers. This explains why 11.25 is added for the marked weekdays. If you could consider these variable, it might help you.
Alternately, you could use an IF statement for Fridays and Saturdays, forcing them to multiply with 4 and 7.25 respectively. Something like: IF(E13="F",COUNTIF($E$14:$Y$14,"D")*4,IF(E13="S",COUNTIF($E$14:$Y$14,"D")*7.25,COUNTIF($E$14:$Y$14,"D")*11.25))
You might want to use a little longer abbreviation or alternate symbol for the days, or Saturday and Sunday would be calculated the same.
From the image and the visible formula, I see no consideration of time or week numbers. This explains why 11.25 is added for the marked weekdays. If you could consider these variable, it might help you.
Alternately, you could use an IF statement for Fridays and Saturdays, forcing them to multiply with 4 and 7.25 respectively. Something like: IF(E13="F",COUNTIF($E$14:$Y$14,"D")*4,IF(E13="S",COUNTIF($E$14:$Y$14,"D")*7.25,COUNTIF($E$14:$Y$14,"D")*11.25))
You might want to use a little longer abbreviation or alternate symbol for the days, or Saturday and Sunday would be calculated the same.