Excel 2007 - conditional formula based on value in different cell?
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?
hi,
can you please attach a spreadsheet with some sample data? it would be easier to solve it if we have the spreadsheet.
thanks,
Ardhendu
0
JSpence8Author Commented:
OK, done. Two different versions in this example - rows 1-4 and rows 7-10. In the first example, what I do is manually enter a SUM formula into column L to calculate the necessary total.
What I would like to be able to do is to setup the spreadsheets like rows 7-10 - with a predefined formula in L that would automatically sum based on the value manually entered in M. Using John Doe as an example, entering 4 in M9 would somehow automatically generate the value of =SUM(B9:E9) in L9. ExpertsExchange-26992487.xlsx
can you please attach a spreadsheet with some sample data? it would be easier to solve it if we have the spreadsheet.
thanks,
Ardhendu