Conditional Calculation Formula

[LBM]

I have a regression type formula that works fine for predicting students scores based on the average of their 2 previous assessments. Here's where I need help.

Here's the current formula where:
P2 = current test score
T2 = last years score
X2 = year before last score
0.92 = regression factor

=P2-(0.92*(AVERAGE(T2,X2)))

I need the formula to do several things that I am not sure how to create.

If current test score (P2) is missing then do not compute the formula.

If either T2 or X2 are missing then use 0.83 rather than 0.92 as the regression factor

If both T2 or X2 (previous score or score from year before last) are missing then do not compute the formula.

Once I get the formula, I will drag it down the column for all rows to compute.

Thanks for any assistance.

[darkyam]

=IF(OR(P2=0,T2+X2=0),"",P2-(IF(OR(T2=0,X2=0),.83,.92))*Average(T2,X2))

[Portuga]

Hi,

The formula above does not take into account test results that were zero.
(Missing ≠ 0)

You can use this modified one: (If blank then it doesn´t calculate, if Zero it does)

Please Login or Register  to view this content.

[darkyam]

Portuga,
True enough, but I figured the odds of a student actually scoring a 0 on an exam are so small that the formulas were practically the same either way. I was a TA for accounting in college and the lowest score I ever saw was still in the 40s. Your formula is, of course, still technically more correct.

[Portuga]

What about those bold copy/paste students...that are caught?

[LBM]

Darkyam and Portuga:

Thanks to you both for your quick and correct responses!!! I did need the code to not calculate for blank scores since it is a matter of missing a state testing administration rather than actually scoring 0.

Again, thanks!!!! This forum and your help is a lifesaver!

LBM