Excel 2007 - conditional formula based on value in different cell?
Posted on 2011-05-03
Not sure whether this can be done in Excel or not, but here goes.
I am using a spreadsheet to track student attendance in a 10-week course - so column A has student name, and columns B-K each track the total attendance hours for that particular week.
These students are tested periodically/randomly, and their test scores together with the total hours of instruction as of the testing date are entered into another database. So for example, if a student tests during week 5, and s/he has attended 9 hours each week through week 5, I would sum the attendance totals from columns B to F and get 45 hours of instruction at the time of testing (9*5), and then enter 45 into the external database.
Normally I do this manually - when a student tests, I insert a SUM formula into column L and then define the SUM range manually with click/drag from column B as far right as I need to (in this case, to column F.) But I have to do this separately for each student (i.e., row) in the sheet.
What I'd like to do is to add two new columns - L with a predefined formula and M with a value used in that formula - to every row in the sheet. Then I could manually enter the week 'number' (call it 'n'), into M, and that value would be automatically referenced in the formula in L to automatically calculate the SUM range from week 1 to week 'n'. In other words, if n = 2, then sum B thru C. If n = 3, sum B thru D (and so on.)
Entering the week number is something simple enough that I could pass the work on to a lower level staffer, but I don't trust anybody else to edit the original manual formulas.
Seems to me that what I want is a sort of CASE statement, in database speak. But not sure how (or even if) that sort of thing is supported in Excel. Anyone have any ideas?