Excel 2007 - conditional formula based on value in different cell?

Posted on 2011-05-03
Last Modified: 2012-05-11
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?
Question by:JSpence8
    LVL 20

    Expert Comment

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


    Author Comment

    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
    LVL 50

    Assisted Solution


    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


    cheers, teylyn
    LVL 20

    Accepted Solution

    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 -




    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    737 members asked questions and received personalized solutions in the past 7 days.

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

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now