Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 columns as shown below (also have attached a sample excel)
I would like to do a conditional coloring but ... here comes the challenge
The first column is the TIMESTAMP which is a record poll of every 10 minutes.
I want to to show the BW Level % as
YELLOW if it is above 75%
GREEN if below 75%
RED if above 75% but sustaining for more than 10 minutes continuously.
We can create a formula to say whether it is above 75% or not but I dont know how to analyze if BW level was sustaining continuously for more than 10 minutes.
Really dont know how to do this and need you help please.
TimeStamp | BW % Level |
4/20/16 12:00 AM | 90% |
4/20/16 12:10 AM | 10% |
4/20/16 12:20 AM | 10% |
4/20/16 12:30 AM | 10% |
4/20/16 12:40 AM | 10% |
4/20/16 12:50 AM | 10% |
4/20/16 1:00 AM | 10% |
4/20/16 1:10 AM | 80% |
4/20/16 1:20 AM | 85% |
4/20/16 1:30 AM | 92% |
4/20/16 1:40 AM | 90% |
4/20/16 1:50 AM | 10% |
4/20/16 2:00 AM | 10% |
4/20/16 2:10 AM | 10% |
4/20/16 2:20 AM | 10% |
4/20/16 2:30 AM | 10% |
4/20/16 2:40 AM | 10% |
4/20/16 2:50 AM | 90% |
4/20/16 3:00 AM | 10% |
4/21/16 12:00 AM | 10% |
4/21/16 12:10 AM | 10% |
4/21/16 12:20 AM | 10% |
4/21/16 12:30 AM | 10% |
4/21/16 12:40 AM | 10% |
4/21/16 12:50 AM | 90% |
4/21/16 1:00 AM | 10% |
4/21/16 1:10 AM | 10% |
4/21/16 1:20 AM | 10% |
4/21/16 1:30 AM | 10% |
4/21/16 1:40 AM | 10% |
4/21/16 1:50 AM | 10% |
4/21/16 2:00 AM | 90% |
4/21/16 2:10 AM | 10% |
4/21/16 2:20 AM | 10% |
4/21/16 2:30 AM | 10% |
4/21/16 2:40 AM | 10% |
4/21/16 2:50 AM | 10% |
4/21/16 3:00 AM | 10% |
Is this how the raw data comes for you? in percentage form?
try this
BW Level % as
YELLOW if it is above 75%
GREEN if below 75%
RED if above 75%
=if([ BW Level %]>75%, yellow(), if([ BW Level %]<=75%, Green() ,Red())
I think the red color is tricky here chanty, it may not be always correct if all the two conditions are not met
Hi Vijay ,
Can you high the rows which must be in red in your above data
Yes this is how I get the raw data..
Trick we could say if it is above or below 75% but We have to see if it is above 75% and continuously sustaining for more than 10 minutes then I have to say it as RED
In your data you posted do you have any of the rows which have %>75 and last long for more than 10 minutes
You will probably need to look at the last row of data to see if that breaches the %age then use that as an AND in your conditional formatting expression.
See the entries below pls
4/20/16 1:10 AM | 80% |
4/20/16 1:20 AM | 85% |
4/20/16 1:30 AM | 92% |
4/20/16 1:40 AM | 90% |
4/20/16 1:10 AM | 80% |
4/20/16 1:20 AM | 85% |
4/20/16 1:30 AM | 92% |
4/20/16 1:40 AM | 90% |