Forum Discussion
Creating a Formula
Hello. I have a list of six numbers that represent the number of hours worked in each of the six days of the week (Mon thru Sat). If the employee worked all six days, I can total the column, no problem. However, if the employee did not work on the 3rd day of the week (=0 hours) I have to show the total number of hours for the first two days; the "0" hours for the third day; and, then the total number of hours for the remaining three days.
Likewise, if he did not work the 3rd and 4th days, I have to show the total number of hours for the first two days; the "0" hours for the 3rd and 4th days; and then the total number of hours for the remaining two days. And so on. As you can imagine there are a variety of configurations across all employees in my spreadsheet.
Can you help me write a formula that will tell Excel to do that for me? Or am I back to manual calculations??
| Date | Hours | Calculation |
| 3-Sep | 15 | |
| 4-Sep | 15 | |
| 5-Sep | 15 | 45 |
| 6-Sep | 0 | 0 |
| 7-Sep | 15 | |
| 8-Sep | 15 | 30 |
Thanks so much.
Gaye
Phil, you are amazing!! Thank you so much...it works!! You have no idea how much time will be saved by using this calculation and I could not have come up with it if I tried from now to forever...
Gaye
In you calulation column try this. =IF(B2=0,0,IF(B3=0,SUM($B$2:B2)-SUM($C$1:C1),""))
It should do everything you were looking for. I've attached a workbook with your example.
Phil, you are amazing!! Thank you so much...it works!! You have no idea how much time will be saved by using this calculation and I could not have come up with it if I tried from now to forever...
Gaye
Recently Mike Girvin showed a video about a special kind of grouping which would apply here - using a helper column in a query.
