Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Named Ranges II (2003 SP1)

    I started a "Named Ranges" thread in January 2004 that was very useful. Now I need to improve on it - there must be a simpler way (??)

    I am constructing a gradebook - students in rows, assignments in columns. First row and first column reserved for labels.

    I would like to be able to do something like the following:
    1) Have a weighted average in column B (this column will be formatted as a number, and have an entry in B2 like =0.7*C2+0.3*D2 in the top left.
    2) Have assignments in columns C and D (these columms will be formatted as numbers, and have numbers entered)

    But ... I would like to do something with named ranges - so that column C is Exam_1 and column D is Exam_2. This is easy to do with one student - just name C2 and D2 with those names, and use them in the formula.

    However, I will have more than one student. So, Exam_1 and Exam_2 will be a named range of 1 column and n+1 rows (including the label at the top of each column). If I put an integer list to number the students starting with 1 in A2 I can then use offset in B2 like this to generate a total:

    =0.7*(offset(Exam_1,A2,0,1,1))+0.3*(offset(Exam_2, A2,0,1,1)

    This formula does what I want, and it can be copied down column B to create totals for everyone.

    My problem is that I'm thinking there has got to be a more elegant way to do this. How on Earth am I going to explain this to my colleagues?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges II (2003 SP1)

    I fI understand correctly, I think you can define Exam_1 and _2 so they refer to the entire part of the column containing numbers. Then you can simply use

    =Exam_1*0.3+Exam_2*0.7

    And copy down.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Ranges II (2003 SP1)

    If you want to do names, instead of naming the entire column, just name 1 cell but have a "relative row":
    select B2,
    Insert -name -define
    Name:Exam_1
    refers to:
    =Sheet1!$C2
    [Note the $ in front of C to lock it and not locked on "2" so it is relative]
    <Add>
    Name:Exam_2
    refers to:
    =Sheet1!$d2
    <ok>

    Now in B2 you can use:
    =0.7*Exam_1+0.3*Exam_2
    and copy it down the rows. Each "exam" will refer to the score in C or D for that particular student/row.


    Personally, I don't understand why you would want to use named ranges. If i understand your setup correctly, i would modify it and start the date in row 3. Leave row 2 for your labels and in row 1 add the "weights" (0.7,0.3, etc).

    Then in B3 you could use a formula like:
    =SUMPRODUCT(C33,$C$1:$D$1)

    to sum the weighted values. the advantages of this are that it can be extended to more columns without having all the +wt3*Exam3 + Wt4*exam4, etc when you add more exams. You can just have it for the entire range:
    =SUMPRODUCT(C3:O3,$C$1:$O$1)

    and it will sum as you add more weights and scores.

    It is also much easier to see the weights and modify them since they are listed on the top and not in every formula.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Named Ranges II (2003 SP1)

    REPLY TO PIETERSE:

    Uh ... I feel dumb ... now

    This works just fine.

    I guess I should've asked better informed people first, rather than goofing around with offset for so long.

    BUT I HAVE ONE LAST QUESTION: I guess the use of offset is necessary if my named range is an array, but not necessary if my named range is a column. Is this correct?

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Named Ranges II (2003 SP1)

    REPLY TO SDCKAPR:

    You made 2 suggestions:

    1) I had thought of this, but it seemed harder to me to explain to others.
    2) I had not thought of this in this particular context (I am not as good as you guys, but I would have a hard time explaining that one around here) - but how would I put my names into sumproduct to get this to work?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Ranges II (2003 SP1)

    1) Jan Karel's method works the same as mine, is more understandable, and is less prone to some errors when naming. I prefer his method.

    2) I don't understand why you need to use names at all in this case? This eliminates the need for the names (though you could name the range of "weights" if desired.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Named Ranges II (2003 SP1)

    With regard to # 2:

    a) I am trying to use names to make the file more transparent and understandable for users.
    [img]/forums/images/smilies/cool.gif[/img] As to why I would want to use names in a sumproduct, well, it is the same reason for using names in any formula - to make it easier for someone to figure out who didn't design it.

    So, having said that, is there a way to use named ranges in sumproduct, or should I stick to Jan Karel's suggestion if names are more important to me than an elegant formula?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Ranges II (2003 SP1)

    Still Not sure I see the need, but here goes:

    If your weights are in row1 (Assume C1:O1 is named "Weights")

    You can use the formula in B3:
    =SUMPRODUCT(C3:O3,Weights)

    and copy it down the rows.

    If you want to name your exam scores: assume they start in row 3 and go to row 100 (range= C3:O100, named "ExamScores")

    The you could place in B3:
    =SUMPRODUCT(INDEX(ExamScores,ROW()-2,0),Weights)

    To me the "unnamed" version is more clear. If you want to name individual scores, I think Jan's way is better than sumproduct. I prefer sumproduct since you don't have to explicitly list each item:

    To do this without sumproduct would be:
    =Wt1*Exam1 + Wt2*Exam2 + Wt3*Exam3+ Wt4*Exam4+ Wt5*Exam5+ Wt6*Exam6+ Wt7*Exam7+ Wt8*Exam8+ Wt9*Exam9+ Wt10*Exam10+ Wt11*Exam11+ Wt1*Exam12+ Wt13*Exam13
    [Assumes each column is named Exam1 to Exam 13 respectively and cells C1:O1 are named Wt1 to Wt13 respectively.]

    Sumproduct is "cleaner" and allows simply inserting a column to expand the range and add more scores. Naming just gets more complicated (In my opinion)

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Named Ranges II (2003 SP1)

    Thanks. Now that I see what's involved I don't see the need either.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •