I want to highlight 3or more consecutive values in a row that are above 4.5.
I thought I had it with = and(e3>4.5,f3>4.5,g3>4.5)
doesn't work. I've tried a couple of different formulas and it always highlights pieces rather than the three or more in a row that are above the 4.5 constant.
=and($e1>4.5,$f1>4.5,$g1>4.5)
Ben Van Johnson
I cant get that to work in the sheet
works for me
I'm trying to figure out how your values changed from the sheet I uploaded...
I cant get it to work across the entire sheet. Most of the values above the 4.5 for three consecutive hours will be on the right of the sheet.
1. I just used random numbers to test the formula since your original didn't have any that met the spec.
2. In your first post you said: "...I want to highlight 3 or more consecutive values in a row that are above 4.5. ..." Meaning (to me) that all three cells in same row of columns e, f & g must exceed 4.5.
3. However, in post #5 you said: "... work across the entire sheet...". Does that mean you want to highlight any three adjacent cells across columns E to AC?
yes, exactly
No one else??
I'm still working on it and cannot get consistent results. Sometimes 3 in a row highlight, sometimes just a single cell highlights.
Dave
That's the problem I'm having. The formula I used in the OP is hit and miss also...
Thanks for the help.
I was trying to get out of having to do this with VBA
Here is a low tech proposal that hopefully someone can build on to provide a better solution.
A helper table, AE2:BB31, is populated using: =OR(AND(E2>4.5,F2>4.5,G2>4.5,ISNUMBER(E2),ISNUMBER(F2),ISNUMBER(G2)),AND(AC2=FALSE,AD2=TRUE),AND(AB2=FALSE,AC2=TRUE,AD2=TRUE))
The conditional formatting rule is: =AE2
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Please try Insert column AC
CF formula applies to
A2:AB31
Formula:
Please Login or Register to view this content.
Last edited by Bo_Ry; 03-23-2020 at 01:18 AM. Reason: correction
You can do this with a single conditional formatting formula, applied to E2:AB30;
Formula:Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
I agree with olly although I had previously used a solution around countif(C2:E2,">=4.5") instead of AND(COUNT(C2:E2)=3,MIN(C2:E2)>=4.5). But both work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks