CODEWITHSUNDEEP
excel
Moosa
Moosa

Reputation: 3216

Excel formula to calculate YTD metrics by detecting current month

I have two excel tables - 2015 sales and 2014 sales in the following format. And a third table called Year over Year Variance which looks at % change in 2015 over 2014.

2015          Jan     Feb    Mar .... YTD (total)
Orders
Revenue
...
...

2014          Jan     Feb    Mar .... YTD (total)
Orders
Revenue
...
...

Variance      Jan     Feb    Mar .... YTD
Orders
Revenue
...
...

Computing Jan variance over Jan or Feb variance over Feb is simple. How do I compute Year to date variance. Because the 2014 table is already populated for the whole year - so the last column is the sum of 12 months of data.

For 2015, I only have Jan and Feb. So the variance column for Year to date should be (Jan+Feb 2015)/(Jan+Feb 2014). How do I write a formula to check for the current month and then sum only those two months for the year to date calculation?

Upvotes: 0

Views: 16238

Answers (2)

Ralph J
Ralph J

Reputation: 478

=Month(Today())

will return the current month (integer, 1-12) in the spreadsheet.

Month(Now)

will do the same thing in VBA code.

I'd use a formula in the 3rd table along the lines of

=if(Month(Today())<=Month(*date-of-column-header*), *Computed-Variance*, 0)

That way, you simply have zero show up in the cells where the comparison makes no sense, and your sum of the numbers in the row is unaffected.

Upvotes: 0

basodre
basodre

Reputation: 5770

Using Excel formulas (no-VBA), you can couple the SUM, OFFSET, and COUNT functions to calculate the variance. In the simplified formula below, Row 1 is fully populated across all 12 months. Row 2 is only populated for 2 months. Using the COUNT formula across the range, the OFFSET will expand the width based on the number of populated rows.

Let me know if you have any problems adapting it.

=SUM(OFFSET($A$2,0,0,1,COUNT($A$2:$L$2)))/SUM(OFFSET($A$1,0,0,1,COUNT($A$2:$L$2)))

Upvotes: 1

Related Questions