I have an Excel 2010 worksheet which calculates the total expected annual rates of pay for contractors.
Against the name of a contractor is :
a) a daily rate of pay in £
b) month columns from Jan to Dec with either a value of 1 (contractor expected to work) or 0 (contractor expected not to work)
c) the average number of days per month (ANDM
) a contractor is expected to work - 21.7 in this case.
d) the total amount of pay expected to be earned by the contractor for the whole year, calculated by multiplying each month (which has a value of 1) by the ANDM multiplied by the daily rate of pay.
I'm struggling to come up with a solution for item d).
I guess the answer is an array formula but I'm not sure how to accumulate the monthly amounts based on whether they are expected to work.
I've attached an example file.