03-12-2019 10:09 AM
03-12-2019 10:09 AM
I'd like to create a formula that either sums columns D2 through H2 OR D2-G2 plus I2. In other words, for the second option I want it to skip H2. Can this be done and if so how do I do it? Screen shot attached.
03-12-2019 11:32 AM
Sorry, that didn't work. Let me try to explain this better. I want a formula for cell C2 (Total Score) that does this:
IF H2>0, then C2 =SUM(D2:H2).
If H2<1, then C2 =SUM(D2,F2,G2,I2).
03-12-2019 12:25 PM
Ahmad, the formula you gave me ALMOST works, but for some reason A student who chooses option 2 comes out at a 90 instead of 100. See attached.
03-12-2019 01:23 PM
Oops, I spoke too soon. It still doesn't work. See screenshot attached. Test Student Option 2 should be a 100. The next row down is a real world (not perfect) score and should be an 82 but it's scoring out at 74.
03-12-2019 02:38 PM
If I use this formula
that gives a total score of 100 for all three rows.
This is an assignment that has two options. Students can either write a paper on a finished research project (option 1: scored in column H) or they can write a paper presenting 2 hypotheses and a research plan (option 2: scored in columns J-L which are then summed in column I). They will have a score in column H or column I, never both.
All students, regardless of which option they choose, will have some points in columns D-G that need to be included in their total score.
Maybe this just can't be done?
03-12-2019 02:51 PM
Sergei, I don't want to delete E2--there should always be points in columns D-E, plus points from either H or I (not both). Removing E2 from your formula leaves
=SUM(D2:I2)*(H2=0) results in a score of 0 for Test Student Option 1
=SUM(D3:I3)*(H3=0) results in a score of 100 for Test Student Option 2
Both should be 100.
03-12-2019 02:53 PM
If only one or the other of H2 or I2 has a non-zero value, i.e., a student could't have marks in both Option 1 and Option 2, then just using the formula
should always work.