Prediction formula

**karnold** · 06-19-2012, 09:39 AM

This is likely an easy one but I cannot seem to figure something that works. What I am trying to do is predict how many complaints my company will have during the fiscal year (from April 1 to March 30). Currently the calculation is being done manually base on the current complaints we have received this fiscal year.

Here is the formula:
- Number of current complaints / total of current fiscal days * 365=
- 31 complaint so far / 80 days into fiscal year * 365 = 141 complaint for 2012/2013

Thank you,
Karnold

**amotto11** · 06-19-2012, 09:57 AM

will this work?

**ChemistB** · 06-19-2012, 10:17 AM

=ROUND(A1/(TODAY()-"4/1/2012")*365,0)
where A1 contains # of complaints so far this year. Assumes it's not a leap year.

**karnold** · 06-19-2012, 10:21 AM

Thanks Amotto11. That could work if it is the only option.

In the spread sheet I only have one cell among a bunch of other stats so I am unable (company doesn't want) add cells for dates. Is it possible to have the dates in the formula which will automatically update or where I can manually change the dates myself?

Karnold Example1.xlsx

Thanks

**karnold** · 06-19-2012, 10:29 AM

Thanks ChemistB. That is exactly what I was looking for. I will have to figure something out for the leap year. Either the stats are off by a very small margin or I manually change the year in the formula for those leap year.

**amotto11** · 06-19-2012, 10:29 AM

Try this formula in D1 instead, then can you make it fit what you need?

=ROUND((B2/(TODAY()-DATE(2012,4,1)))*365,0)

**karnold** · 06-19-2012, 10:39 AM

Thanks ChemistB and Amotto11. That is exactly what I was looking for. I will have to figure something out for the leap year. Either the stats are off by a very small margin or I manually change the year in the formula for those leap year.