Excel Getting an annualized figure based on YTD in Excel

[Tinkerer20]

I am trying to get an annualized sales figure based on YTD sales. I want this to be linked to a today() function so that the spreadsheet only includes figures from today's date or before when calculating the annualized total. I have a table with months and corresponding totals shown below each month (starting in April). And thought I could use a formula including sumif that would tally and annualize all numbers with corresponding months less than or equal to the month from today's date.

Something along the lines of:
=SUMIF(B8:M8,"<=H3",(B10:M10))

B8:M8 was the row of months (April-Mar)
B10-M10 was the row of sales figures from those months
H3 was a "text" formula, extracting the month from a corresponding "today()" formula.

For some reason this only seemed to take the figure from the first month (April). Not sure where I am going wrong?

Any help is appreciated.

 

[Roniou2]

If what I think you are trying to achieve is as follows:

That if the total of the sales by August is 1000 that on the basis this is your five month that your average per month so far you have sales of 1000/5 = 200. This would mean therefore over the 12 months you should achieve 200*12 = 2400.

To achieve this I would use the name function and creat a name representing the current month in your year. As you start in April then this would be month 1 and May would be month 2 etc. To find the current month you can use Month(Today()) this will give a value of 4 in April and 12 in Dec and 1 in Jan. This then needs to be amend to get to your month value. As you are start in April you will need to subtract 3 from the answer in the months April to Dec and add 9 in the months Jan to March. The following formula will do this:

=IF(MONTH(TODAY())<=12,MONTH(TODAY())-3,MONTH(TODAY())+9)

This formula will then give you the value currently of 5 being August which is your fifth month.

The above formula can be copied and pasted into name manager using the new name CurrentMonth

Having created this name you now need to only select the sales in the correct range. Again to do this I would create another name say YTDSales.

Then by using the offset formula you can combine this with the CurrentMonth and it will only sum the months to date. In this formula the width of the range is set by the CurrentMonth. This means that in August it will be CurrentMonth = 5. Therefore, the offset will go from B10 your starting cell to four columns to the right.

The thing to make sure is that your Sheet is either the first one for this formula to work or you need to change the name of shee1 to the name of your sheet.

=OFFSET(Sheet1!$B$10,0,0,,CurrentMonth-1)

The above formula can be copied and pasted into name manager using the new name YTDSales

Having created these two names you can then go to the cell where you want the total projected annualised sales to be and add the following formula to that cell:

=SUM(YTDSales)/CurrentMonth gives the average per month to date this therefore needs to be multiplied by 12 to get the annualised figure so the final formula should be:

=(SUM(YTDSales)/CurrentMonth) *12

Hope that helps.