Richard_McConnell
New Member
- Joined
- Apr 8, 2015
- Messages
- 5
I am building a Project Budget sheet and I am trying to shorten the formulas that provide sub-totals. I would be grateful if anyone could help.
Staff names are represented in the first column followed by their day rate cost in the next. The subsequent columns represent weeks of the year. Each member of staff is on a different rate and they will not all contribute the same time as each other in any given week. I would normally use this formula to calculate the cost of an employee in a week as:
B2 (staff cost rate per day) * B3 (work carried out that week in days/part days). This would give me a cost for that member of staff for the week. Straightforward.
However, in order to give a total staff cost for the week, I would need to add a similar formula for every employee for that week giving a very long formula (B2*B3)+(C2*C3), etc. I would then have to repeat that formula sequence for the staff for all subsequent weeks. The importance of calculating this information is to gain a set of weekly staff costs. There could be as many as 50 employees and I would need to allow room for additional staff part way through a project.
There must be a way of shortening such a repetitive formula. My apologies if this appears Excel 101 territory.
Staff names are represented in the first column followed by their day rate cost in the next. The subsequent columns represent weeks of the year. Each member of staff is on a different rate and they will not all contribute the same time as each other in any given week. I would normally use this formula to calculate the cost of an employee in a week as:
B2 (staff cost rate per day) * B3 (work carried out that week in days/part days). This would give me a cost for that member of staff for the week. Straightforward.
However, in order to give a total staff cost for the week, I would need to add a similar formula for every employee for that week giving a very long formula (B2*B3)+(C2*C3), etc. I would then have to repeat that formula sequence for the staff for all subsequent weeks. The importance of calculating this information is to gain a set of weekly staff costs. There could be as many as 50 employees and I would need to allow room for additional staff part way through a project.
There must be a way of shortening such a repetitive formula. My apologies if this appears Excel 101 territory.
