can you please attach a spreadsheet with some sample data? it would be easier to solve it if we have the spreadsheet.

thanks,

Ardhendu

Solved

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?

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?

4 Comments

can you please attach a spreadsheet with some sample data? it would be easier to solve it if we have the spreadsheet.

thanks,

Ardhendu

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

the title of the question is very misleading. Conditional formatting is not what this is about.

Here is a formula that does what you describe. Enter into L9 and copy down

=SUM(B9:INDEX(B9:K9,MATCH(

cheers, teylyn

Teylyn has already given you the right answer. The formula she has given would do the trick except if the value in Column "Week" was equal to 1.

I am suggesting a slight modification to the formula which will take care of Week 1 summation too -

=IF(M9=1,B9,SUM(B9:INDEX(B

Thanks,

Ardhendu

ExpertsExchange-26992487.xlsx

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel 2010 - Select category per Max values v.2 | 2 | 17 | |

IF cell equals certain value then display text in cell | 4 | 32 | |

Excel 2016 - AutoFilter on selected columns issue | 5 | 18 | |

Excel totals by filter - Automated | 3 | 11 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**22** Experts available now in Live!