I use a simple spreadsheet to track my sales people. It displays their sales figures as follows.

Monthly turnover target in Rows A4:L4

Actual sales figures in Rows B4:L4

Monthly % versus target in Rows C4:L4

Annual YTD % in Rows D4:L4

The columns are headed by month January to December.

In M4:M7 is the summation of the relevant row. However, I need a simple formula that will display the YTD % in Cell M7. Because there are no sales for the months to come in the year then it calculates incorrectly. I know that the correct figure is shown in the relevant cell of Row D4:L4 but my boss needs to see it in the summation column.

Is there a formula that will calculate the % YTD and recognize that there are blank months and that we are only part way through the sales year? For example a seller that has sold £150,000 against an annual target of £200,000 is 75% of his target and someone who has sold £210,000 is 105% with several months still to come.

I have attached a small sample of the sheet.

All help gratefully received

Thanks in advance

Dave