+ Reply to Thread
Results 1 to 6 of 6

Conditional Calculation Formula

  1. #1
    Registered User
    Join Date
    03-24-2008
    Posts
    4

    Conditional Calculation Formula

    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.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    =IF(OR(P2=0,T2+X2=0),"",P2-(IF(OR(T2=0,X2=0),.83,.92))*Average(T2,X2))

  3. #3
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    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.
    Last edited by Portuga; 03-24-2008 at 12:04 PM.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    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.

  5. #5
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    What about those bold copy/paste students...that are caught?


  6. #6
    Registered User
    Join Date
    03-24-2008
    Posts
    4

    Thumbs up Thanks

    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
    Last edited by LBM; 03-24-2008 at 03:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1