• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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?
0
JSpence8
Asked:
JSpence8
  • 2
2 Solutions
 
Ardhendu SarangiSr. Project ManagerCommented:
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
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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(M9,$B$7:$K$7,0)))

cheers, teylyn
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi Spence,

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(B9:K9,MATCH(M9,$B$7:$K$7,0))))

Thanks,
Ardhendu

ExpertsExchange-26992487.xlsx
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now