# offset form address in cell referenced

1. ## offset form address in cell referenced

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. ## 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

3. ## 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

4. ## 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. ## 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. ## Re: offset form address in cell referenced

Who are you talking to?

7. ## Re: offset form address in cell referenced

I'm assuming you Glenn

I think your interpretation makes more sense.

8. ## Re: offset form address in cell referenced

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

9. ## Re: offset form address in cell referenced

Originally Posted by CK76
I'm assuming you Glenn

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

10. ## Re: offset form address in cell referenced

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

12. ## Re: offset form address in cell referenced

it was to glenn. yes correct. i got it with this in conditional formating:
these are all basic formulas. i dont know how to use frequent and you have tons of parenthesis.

13. ## 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. ## Re: offset form address in cell referenced

Does it work??

I can't seem to get it working.

15. ## 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.

Page 1 of 2 1 2 Last

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

#### 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