Excel Formula for ongoing Quarterly Percentages

[Manny74]

I am having trouble with my Q1 formula.....
We tend to input the number of charts we have reviewed, in row 3 (Jan -17 charts, Feb -18 charts, etc)...
We are supposed to audit and review about 20 charts a month.
So for January, I see how we got 85% (we reviewed 17 charts out of 20, so 85%)
What I need help with is developing a quarterly formula, because right now, if I follow the same formula we set up below, for Q1 we get 58%, but that is not right, when we got 85% in Jan and 90% in Feb....
I thought about using an Average for Q1 (G4) but wasnt sure if that would be appropriate or accurate.
Is there a better formula to use to give you an accurate ongoing quarterly percentages?
When we input the number of charts for Jan, Feb & March, the quarterly numbers look accurate, it's when we are not done reviewing charts for 3 full months, that Q1 doesnt look accruate....
D E F G

		JAN	FEB	MAR	Q1
3	Number of Charts Reviewed	17	18		SUM(D3:F3) = 35
4	Percentage (based on maximum 20 charts)	D3/20*100 = 85	E3/20*100 = 90	F3/20*100	G3/60*100 = 58

 

[pjmorris]

In truth, the calculation of 58% is entirely correct as you've not done any audits in Mar. This means the 58% is an accurate calculation that shows the minimum you'll achieve in the quarter. The table below 'fudges' the answer by calculating averages of the two cells that have data in - it attempts to do this regardless of which month(s) are missing. Whatever it calculates though is a a fudge and therefore wrong. But if it helps answer the bosses question!!

The first term in cell E3 merely calculates the sum of the three months. The next set of clauses in E3, such as (B3=0)*(C3+D3)/2 calculate the average of cells C3 and D3 if B3 is 0 (B3=0 is true and this is used as 1 in the calculation, it avoids a potmess of IF statements).

Book1
	B	C	D	E
2	Jan	Feb	Mar	Q1
3	17	18		52.5
4	85%	90%	0%	88%
Sheet1

Cell Formulas
Range	Formula
E3	E3	=SUM(B3:D3)+(B3=0)*(C3+D3)/2+(C3=0)*(B3+D3)/2+(D3=0)*(B3+C3)/2
B4:D4	B4	=B3/20
E4	E4	=E3/60