Grumpy88
Member
Hi all.
I can't upload a sample file, so hopefully the following explanation will suffice for a solution to be forthcoming from someone here.
I have a spreadsheet where row 33 contains month-by-month totals for 2011 (cells B33: M33 for Jan to Feb), while cells B34:M34 do the same in row 34 for the corresponding 2012 figures.
In another cell I then have a formula to calculate the relationship between the 2012 and 2011 figures, in terms of the 2012 year-to-date figures as a percentage of the 2011 year-to-date figures at the same point. In other words, if the total for the first six months of 2012 is 200 for example, and in 2011 the first six months totalled 300, then the formula must calculate 200/300 = 67%.
At present the formula used is =SUM(B34:G34)/SUM(B33:G33). This works, but the problem is that every month this formula must be manually adjusted to include the next month's column (i.e. when July 2012's figures become available and are entered in cell H34, the formula must be adjusted manually to =SUM(B34:H34)/SUM(B33:H33). This is because the rest of row 34 is blank, until the 2012 figures become available each month. This means that the formula cannot just be initially set to =SUM(B34:M34)/SUM(B33:M33) and left as such, because the blank cells in row 34 will throw out the calculation.
Is there a formula that I can use that will automatically detect blank cells in row 34, and disregard the corresponding columns in row 33 when calculating the year-to-date percentage relationship between the two rows?
Thanks!
I can't upload a sample file, so hopefully the following explanation will suffice for a solution to be forthcoming from someone here.
I have a spreadsheet where row 33 contains month-by-month totals for 2011 (cells B33: M33 for Jan to Feb), while cells B34:M34 do the same in row 34 for the corresponding 2012 figures.
In another cell I then have a formula to calculate the relationship between the 2012 and 2011 figures, in terms of the 2012 year-to-date figures as a percentage of the 2011 year-to-date figures at the same point. In other words, if the total for the first six months of 2012 is 200 for example, and in 2011 the first six months totalled 300, then the formula must calculate 200/300 = 67%.
At present the formula used is =SUM(B34:G34)/SUM(B33:G33). This works, but the problem is that every month this formula must be manually adjusted to include the next month's column (i.e. when July 2012's figures become available and are entered in cell H34, the formula must be adjusted manually to =SUM(B34:H34)/SUM(B33:H33). This is because the rest of row 34 is blank, until the 2012 figures become available each month. This means that the formula cannot just be initially set to =SUM(B34:M34)/SUM(B33:M33) and left as such, because the blank cells in row 34 will throw out the calculation.
Is there a formula that I can use that will automatically detect blank cells in row 34, and disregard the corresponding columns in row 33 when calculating the year-to-date percentage relationship between the two rows?
Thanks!
