Annual Comparison Percentage Formula Help I am hoping you can help me out here.

Question

Annual Comparison Percentage Formula Help

I am hoping you can help me out here. I am no power user, but am attempting to see if there is a formula I can enter into C15 that would mean I did not have to update each and every time I update data. The goal is to enter our data for each month in rows 1-15 and compare them to the data of last year rows 19-31.

So for instance, I just updated September 2014 and =C4/C21 is what I needed to update in C15 to get the accurate percentage. But is there a formula that would "auto-populate" this informationn with the newestt information added? As I will update C5 at the start of next montth, I'm hoping not to have to re-type the formula in C15 to be =C5/C22. It would be a formula that automatically advances by 1 row each time...same column. Anyone???

Explanation / Answer

The key to this question is deciphering which month is the last to have data entered in it.

Your formula in C15 is pretty simple at the moment. Before contemplating how to pick the correct month from the previous year, there's one small change you can make to make the formula easier - instead of picking C4, C5 etc, you can just use the total in C14 each time, as this will give you the year to date total equal to the last month you've entered data for.

So your formula in C15 should now read =C14/C21.

Next to determine which montth to look at in the previous year.

You can use the COUNT function to determine how many month's you've entered data for as follows

=COUNT(B2:B13)

which will currently return 3.

Coupling this with an OFFSET forrmula will return the relevant figure from column C

=OFFSET(C18,COUNT(B2:B13),0)

So, the final formula would look something liike

=C14/OFFSET(C18,COUNT(B2:B13),0))

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.