Any Excel nerds out there?

I've made an IF function, which assigns various text values to different numbers. If, in the function, I have the actual numbers and do a fill down, no problem. However, I want to be able to change the criteria without opening the function so have assigned the criteria to a cell, thereby enabling me to edit the cell and change the boundaries easily. But when I do a fill down with that it moves the cells down one as well.

This is the function:
=IF(R2>=R60,"4a",IF(R2>=R61,"3a",IF(R2>=R62,"3b",IF(R2>=R63,"3c","2a"))))

When I fill down, R2 changes correctly to R3 and so on, but R60 to R63 ALSO change to R61 and so on, thereby rendering the function full of blank cells after a bit.

Posted By: APB on September 12th 2012 at 21:25:15

Message Thread

Any Excel nerds out there? (General Chat) - APB, Sep 12, 21:25:15
- smell and usb ring any bells? (n/m) (General Chat) - Pixelman, Sep 12, 21:42:50
- I went to the Excel for the Paralympics (General Chat) - Charles21, Sep 12, 21:30:08
  - My paralympic spreadsheet has all sorts of problems with the cells (n/m) (General Chat) - UtterlyHuckerby, Sep 12, 21:46:38
  - Yes. Yes it does, thanks. (General Chat) - APB, Sep 12, 21:35:42
    - haha (n/m) (General Chat) - Charles21, Sep 12, 21:37:42
- Anchor the cells you want to stay the same by using F4 (n/m) (General Chat) - Colney Rodent, Sep 12, 21:28:59
- F4 on the R63 to anchor it. Same for R60 etc. (n/m) (General Chat) - yarmyyarmy, Sep 12, 21:26:59
  - I'm using a Mac. Will F4 do that? (General Chat) - APB, Sep 12, 21:29:27
    - Command-T apparently (n/m) (General Chat) - yarmyyarmy, Sep 12, 21:31:40
    - If it doesn't, add a $ before the cell number. Has the same effect. (n/m) (General Chat) - Yellalee, Sep 12, 21:31:37
      - No, same thing happened. (General Chat) - APB, Sep 12, 21:46:13
        
        $R60 will anchor the column dragging horizontally, R$60 anchors the cell vertcally. $R$60 both horiz (n/m) (General Chat) - Rusty Steele, Sep 12, 21:56:01
        
        R$60 anchors the row. $R$60 anchors column and row. absolute cell referencing (n/m) (General Chat) - Rusty Steele, Sep 12, 21:57:31
        
        No, before the number, rather than the letter. Or both. (n/m) (General Chat) - Yellalee, Sep 12, 21:49:53
        
        both (n/m) (General Chat) - Karnivore, Sep 12, 21:56:00
        
        It should be $R$60 so both the column and row are anchored (n/m) (General Chat) - yarmyyarmy, Sep 12, 21:47:57

Reply to Message

In order to add a post to the WotB Message Board you must be a registered WotB user.

If you are not yet registered then please visit the registration page. You should ensure that their browser is setup to accept cookies.