 # Formula for Running YTD Average

• Hi All,

My basic objective is to look up the cost per month of each project in the previous months and print the average YTD cost in that month in Col F.

For example Project A cost per month in April is 10. In May if it is 20, then in the next column of YTD average, April Cost will be same since it is the first month i.e 10. But in May it will be 15 ((10+20)/2).

Please refer attached xls for reference forum.ozgrid.com/index.php?attachment/62559/.

I tried to use nested ifs with vlookups but not getting anywhere.

Please note there are 50+ projects across 12 months in my data, hence simple average formula that I have use for the example wont work.

Thanks!

## Files

• Re: Formula for Running YTD Average

Try this in F2 and copy down as required:

=SUMIF(\$A\$1:A2,A2,\$E\$1:E2)/COUNTIF(\$A\$1:A2,A2)

• Re: Formula for Running YTD Average

Hi andyb16,

There are a few assumptions here: (1) Your year starts at April, (2) You will only have one year of data in your table, (3) Your YTD cost is based on 'cost per person per month', not just 'cost per month', otherwise you would be basing your calculations on column D (salary total) instead.

Put this formula in cell F2, and fill down:

=SUMIF(A\$2:\$A2,A2,E\$2:E2)/COUNTIF(A\$2:A2,A2)

Hope this helps.

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Formula for Running YTD Average

Hi SO and Batman,

First of all apologies for the late response and thank you for your suggestions.

I am getting the desired results from the above formulas.

HI Batman - thanks for tip - "(3) Your YTD cost is based on 'cost per person per month', not just 'cost per month', otherwise you would be basing your calculations on column D (salary total) instead." You were right. I had to do some changes to my core data.

Thanks!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!