Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    formula for addressing

    Hi All,

    The attached workbook ranks the classes I've taught over the last few years by looking at various stats (class avg, SD, #passing for each test; #passing for semester). Each stat, except SD, is ranked across the classes; then the rankings are added to rank the class as a whole.

    The ranking formula in D3 was created and then filled down the column - easy enough. But what I had wanted to do was create just that one formula and then copy or paste it into the other columns that do ranking and have the parameters of the RANK BIF adjust based on the location of the formula.

    I tried various combinations of OFFSET, INDIRECT, ADDRESS, ROW, and COLUMN but none worked, giving #VALUE errors (and probably others I don't recall). I ended up pasting the formula from D3 into G3, I3, L3, etc and then manually adjusting the RANK BIF parameters for that column.

    Is there anything I could have done that would have been easier and achieved the goal initially expressed (short of any macro)?

    Even the formula I have now is not "safe." In preparing this stripped down version, I tried to delete cols A or B (or both). This resulted in a circular reference for the formula in what is now D3; even without the circular reference, the formula was now wrong. So cols A and B are as originally created.

    TIA.

    Fred
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    I finally had some time to play around with this. I found that using INDIRECT with R1C1 type of addressing worked pretty well and took care of what I needed to do. I never use this type of addressing but it makes working with numbers for columns (eg, being able to refer to COLUMN()-1 ) pretty easy. I don't mind the "R" concatenated with a number, similar for the column, I just have problems remembering the parts of the address are reversed from "normal" A1 type of addressing.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, I get a message about file corruption and loss of data opening that workbook in 2010 and there are no rank formulas to review.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    Thanks for looking.

    I don't have the original file anymore that was posted on the Lounge - it's been revised a few times.

    I tried opening the current version in 2010 and got some of the same messages you probably got. But it did open without my formating and conditional formating that was present in 2003. But the rankings were there.

    So it's probably not worth posting the current version. In fact, beyond my 2nd post from the other day, I did some further work and found I could make things even simpler. For example, I don't need RANK(C3, C$3:COUNT(...)). I can just do RANK(C3,C:C). I was concerned that the 2nd part of the column range had to be a dynamic formula, since I will be adding entries to col C and didn't want blanks to be considered. C:C as the range over which to do the ranking works fine.

    It was probably a useful learning exercise for me, especially learning about INDIRECT with R1C1 addressing since I've never used it. But I think I over complicated the problem bcs of the above.

    Fred

Tags for this Thread

Posting Permissions

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