+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 23

offset form address in cell referenced

  1. #1
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    offset form address in cell referenced

    Why doesn't this work? OFFSET(ADDRESS(1,MATCH(TRUE,INDEX(A1:G1<=65,0),),4,1),0,1)
    Test Scores.xlsx
    Basically my goal is to figure out a formula for conditional formatting the names to turn red if any two adjacent scores are below =<65.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,244

    Re: offset form address in cell referenced

    So... anything in B1:G1 range less than or equal to 65 turn A1 red?

    You can just use Countif.
    =COUNTIF($B1:$G1,"<=65")>1
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    This will work if your criterion is that 2= ADJACENT cells wihin each row are <= 65 (that is what I took from your description).

    =MAX(FREQUENCY(IF(B1:G1<=65,IF(B1:G1<>"",COLUMN(B1:G1))),IF(B1:G1>=65,COLUMN(B1:G1))))>1
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    i got the offset to work with indirect. now i entered this into conditional formating: =if(OFFSET(ADDRESS(1,MATCH(TRUE,INDEX(A1:G1<=65,0),),4,1),0,1)<=65)
    it get an errror window.
    how do i fix this

  5. #5
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    thanks but i would like a simpler formula; an adjustment to my design because im also trying to learn and understand

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    Who are you talking to?

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,244

    Re: offset form address in cell referenced

    I'm assuming you Glenn

    I think your interpretation makes more sense.

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    Be clear: IS your requirement that 2 or more ADACENT cells have to be <=65?

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    Quote Originally Posted by CK76 View Post
    I'm assuming you Glenn

    I think your interpretation makes more sense.
    I assume so, too... but who knows??

  10. #10
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    Nevermind you guys. i got it myself with some googling.

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    So... share your answer for the benefit of others.

  12. #12
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    it was to glenn. yes correct. i got it with this in conditional formating:
    =OFFSET(INDIRECT(ADDRESS(MATCH(A2,A:A,0),MATCH(TRUE,INDEX(A2:G2<=65,0),),4,1)),0,1)<=65.
    these are all basic formulas. i dont know how to use frequent and you have tons of parenthesis.

  13. #13
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    I know you guys write your own formula to answer me but im just trying to tweak my own to work and understand it, not to get a quick answer that is way over my head

  14. #14
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,926

    Re: offset form address in cell referenced

    Does it work??

    I can't seem to get it working.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    11-08-2017
    Location
    miami, florida
    MS-Off Ver
    2010
    Posts
    35

    Re: offset form address in cell referenced

    I tried both of ours, glenn. neither work. i added and(not(isblank(b3).Test Scores.xlsx
    I show some examples.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1