Looking for formula to work with Conditional Formatting

[shaikhrulez]

Please refer to attached file.

I've applied the conditional formatting and getting the desired result based on the values in K4:K32.

But, what actually I'm looking for is a formula to work with the above range,

I want the above range to highlight when

• Values in Rows K4:K32 is greater than 0 BUT with another condition that it also gets highlights if the values in K24, K28, K29, K33, K40, K42 is less than 0

Is there any formula for this?

 

[Khalid NGO]

Hi,

I think there are unnecessary separate formulas applied, whereas you can apply the single CF formula to the whole range i.e. $A$23:$M$23:

=$K5>0

Remove all other rules, and apply this single rule.

In your attachment I found cells till 32. (K33, K40, K42 not found), you need to mention your expected result now, which rows need to be highlighted on what basis.

Regards,

 

[shaikhrulez]

Hi,

I think there are unnecessary separate formulas applied, whereas you can apply the single CF formula to the whole range i.e. $A$23:$M$23:

=$K5>0

Remove all other rules, and apply this single rule.

In your attachment I found cells till 32. (K33, K40, K42 not found), you need to mention your expected result now, which rows need to be highlighted on what basis.

Regards,

Sorry for mentioning wrong fields. I want formula for below result in attached file.

I want the range to highlight when

• Values in Rows K4:K32 is greater than 0 BUT with another condition that if values in K14, K18, K19, K23, K30, K31 & K32 is less than 0 they also gets highlighted.

 

[Khalid NGO]

Sorry for mentioning wrong fields. I want formula for below result in attached file.

I want the range to highlight when

• Values in Rows K4:K32 is greater than 0 BUT with another condition that if values in K14, K18, K19, K23, K30, K31 & K32 is less than 0 they also gets highlighted.

Waqar I am still not clear what is your expected result...

Have you tried removing all other CF rules except I mentioned above?

Can you share your expected result with manual color fill?

 

[shaikhrulez]

I'm afraid why I'm unable to explain my query where am I missing?

I've again attached the file, actually it has already got the desired result as I've explained in my first post, I want the same result as shown in Sample file but through formula.

Let me explain you how achieved this:

I selected the entire range K4:K32 applied the formula as =$K5>0 (you also mentioned the same) for highlighting cells having values greater than 0

Now, 2nd condition, I want K14, K18, K19, K23, K30, K31 & K32 to highlight when their value is less than 0

To meet the 2nd condition, I removed all conditional formatting rules from the rows containing above cells and then applied new rule separately for each row i.e. if =$K$14 <0

That's it.

Now, coming to what I'm looking for.

I want to avoid manual working by including a formula (OR / AND condition) which can apply the desired formatting when the above two criteria meets.

I hope things will be clear to you

 

[Khalid NGO]

OK got something

I have used only 2 CF rules:
One we'd discussed earlier, 2nd with the helper column:
=AND(K4>0,OR(LOOKUP(ROW(),ROW($1:$99),ROW($1:$99))={14,18,19,23,30,31,32}))

See the attached, if I understood correctly then move to apply this helper formula in CF.

Regards,

Edit: with AND

 

[NARAYANK991]

Hi Khalid ,

Isn't the part :

OR(LOOKUP(ROW(),ROW($1:$99),ROW($1:$99))={14,18,19,23,30,31,32})

the same as :

=OR(ROW()={14,18,19,23,30,31,32})

Narayan

 

[Khalid NGO]

Hi Khalid ,

Isn't the part :

OR(LOOKUP(ROW(),ROW($1:$99),ROW($1:$99))={14,18,19,23,30,31,32})

the same as :

=OR(ROW()={14,18,19,23,30,31,32})

Narayan

Oops, My bad...

You are right Sir,
Don't know why did I wrote this

 

[Khalid NGO]

Hi Waqar,
Sorry for the delay.

Please check this:
=OR(AND(ROW()=4,$K4>0),AND(ROW()=7,$K4>0),AND(ROW()=11,$K4>0),AND(ROW()=14,$K4<0),AND(ROW()=17,$K4>0),AND(ROW()=18,$K4<0),AND(ROW()=19,$K4<0),AND(ROW()=23,$K4<0),AND(ROW()=30,$K4<0),AND(ROW()=31,$K4<0),AND(ROW()=32,$K4<0))

Remove all other rules.
It give me very hard time, as conditions are not continues and vary with rows.

Regards,

 

[shaikhrulez]

No problem for the delay You are a champ Khalid

This one did exactly what I was looking for, but would you kindly share how this formula is working? It simply went over my head

 

[Khalid NGO]

No problem for the delay You are a champ Khalid

Thanks for the feedback dear, not a champ, still in learning phase.

This one did exactly what I was looking for, but would you kindly share how this formula is working? It simply went over my head

Understanding it is actually not a big deal if you go step by step.
all conditions are wrapped with OR function, means a single TRUE in all, will makes TRUE.

Go with step by step:
AND(ROW()=4,$K4>0)
it is checking row number is equals to 4 and K4 greater than zero.

AND(ROW()=7,$K4>0)
checking row number is equals to 7 and K4 greater than zero.

similarly this and others:
AND(ROW()=14,$K4<0)
checking row number is equals to 14 and K4 less than zero (etc)

OR will make TRUE if any 1 from these AND condition = TRUE

go through separate AND conditions, I am sure you will get it.

Regards,

 

[shaikhrulez]

Thanks for the feedback dear, not a champ, still in learning phase.



Understanding it is actually not a big deal if you go step by step.
all conditions are wrapped with OR function, means a single TRUE in all, will makes TRUE.

Go with step by step:
AND(ROW()=4,$K4>0)
it is checking row number is equals to 4 and K4 greater than zero.

AND(ROW()=7,$K4>0)
checking row number is equals to 7 and K4 greater than zero.

similarly this and others:
AND(ROW()=14,$K4<0)
checking row number is equals to 14 and K4 less than zero (etc)

OR will make TRUE if any 1 from these AND condition = TRUE

go through separate AND conditions, I am sure you will get it.

Regards,

There are still few things I didn't understand here.

The conditional formatting is starting from 4th row and for that you hv used this step
AND(ROW()=4,$K4>0)
got this, but the second condition is confusing. You've used this
AND(ROW()=7,$K4>0)
for the 7th row why you have again used $K4>0
shouldn't be it like this $K7>0 ??

One thing more, ROW() function is used for Rows 4,7,11 & so on, but skipped some Rows like 5,6,8,9,10,12,13 & so on.

How did the CF working for skipped rows? Is the ROW functions using some sort of Range Criteria or What?

Please explain.
Thanks

 

[Khalid NGO]

AND(ROW()=7,$K4>0)
for the 7th row why you have again used $K4>0
shouldn't be it like this $K7>0 ??

Hi Waqar,
That is the main thing you should note for CF.
CF starts from K4, and 2nd condition is $K4>0

means when it goes for next row, it will become $K5>0 (CF will automatically adjust it, since we have made relative reference, If we had mentioned absolute reference $K$4>0 then CF will not change for the next row.

AND(ROW()=7,$K4>0)
both conditions (i.e. row is 7 and K4 greater than zero) are TRUE
if any 1 of the condition is FALSE, AND will make it FALSE.

One thing more, ROW() function is used for Rows 4,7,11 & so on, but skipped some Rows like 5,6,8,9,10,12,13 & so on.

How did the CF working for skipped rows? Is the ROW functions using some sort of Range Criteria or What?

Please explain.
Thanks

Actually your conditions are dependent on specific ROW numbers, therefore I had mentioned only the specific rows.

CF is working on skipped rows because of AND function, and will work when both conditions are TRUE.

Regards,

 

[shaikhrulez]

Hi Waqar,
That is the main thing you should note for CF.
CF starts from K4, and 2nd condition is $K4>0

means when it goes for next row, it will become $K5>0 (CF will automatically adjust it, since we have made relative reference, If we had mentioned absolute reference $K$4>0 then CF will not change for the next row.

AND(ROW()=7,$K4>0)
both conditions (i.e. row is 7 and K4 greater than zero) are TRUE
if any 1 of the condition is FALSE, AND will make it FALSE.

Alright, I got the logic behind using relative reference, but here one thing is bothering me. Our first condition i.e. greater than 0 doesn't change until Row No. 14 Right?

Then why did you use Row 7 & 11 but not others?

As you said (CF will automatically adjust) then shouldn't it be working all the way from Row 4 to Row 13 without using additional conditions for Row 7 & 11?

 

[Khalid NGO]

Alright, I got the logic behind using relative reference, but here one thing is bothering me. Our first condition i.e. greater than 0 doesn't change until Row No. 14 Right?

Then why did you use Row 7 & 11 but not others?

As you said (CF will automatically adjust) then shouldn't it be working all the way from Row 4 to Row 13 without using additional conditions for Row 7 & 11?

No, actually your conditions are not continuous, and vary with row numbers, therefore I did used AND to make sure it works with specific row number + a condition for greater than or less than altogether.

Lets summarize the long CF formula with this shorten NON CF formula:
=OR(AND(OR(ROW()={4,7,11,17}),$K4>0),AND(OR(ROW()={14,18,19,23,30,31,32}),$K4<0))

First we take look at $K4 > ZERO:
OR(ROW()={4,7,11,17})
This is 1st condition of OR formula and will check the row number is equals to 4,7,11 or 17

If we get a TRUE, then move to the next condition i.e. $K4>0
We need both condition to be TRUE, so we wrapped with AND function
AND(OR(ROW()={4,7,11,17}),$K4>0)

That we are checking our current row is equals to any of the above {row number} and also that cell is i.e. K4 is greater than zero. we will get TRUE if both conditions are TRUE.

2nd OR condition (red) is similar to first except the last part which is less than zero.

Any of the blue or red gets TRUE, row will be highlighted.

 

[Khalid NGO]

Above formulas are for example of specific row condition, you can add more row numbers or amend it as per your need.

 

[shaikhrulez]

Thanks @Khalid NGO for the detailed explanation, I'll go through this and try to grasp it. If come across any issue, will let you know.

thanks a bunch

 

[Khalid NGO]

Thanks @Khalid NGO for the detailed explanation, I'll go through this and try to grasp it. If come across any issue, will let you know.

thanks a bunch

Welcome dear.

You might be confusing with other row numbers like 5,6,8,9,10,12,13
Why i didn't include'em?
I didn't include them as there was no requirement to highlight them.

In case you need row 5,6,8,9,10,12,13 etc to be highlighted, you can add AND CONDITIONS to above formula for example:

AND(ROW()=5,$K4>0)
AND(ROW()=6,$K4>0)
AND(ROW()=8,$K4>0)

AND(ROW()=9,$K4<0)
AND(ROW()=10,$K4<0)
AND(ROW()=12,$K4<0)
AND(ROW()=13,$K4<0)
.
.
.
AND(ROW()=29,$K4<0)

Criteria given above as example only

Regards,

 

[shaikhrulez]

Welcome dear.

You might be confusing with other row numbers like 5,6,8,9,10,12,13
Why i didn't include'em?
I didn't include them as there was no requirement to highlight them.

In case you need row 5,6,8,9,10,12,13 etc to be highlighted, you can add AND CONDITIONS to above formula for example:

AND(ROW()=5,$K4>0)
AND(ROW()=6,$K4>0)
AND(ROW()=8,$K4>0)

AND(ROW()=9,$K4<0)
AND(ROW()=10,$K4<0)
AND(ROW()=12,$K4<0)
AND(ROW()=13,$K4<0)
.
.
.
AND(ROW()=29,$K4<0)

Criteria given above as example only

Regards,

Phew...!, you got me right now.

As soon you mentioned in this post, I just tried converting the data of K5 to positive figure, it doesn't get highlighted. Hence proved you didn't apply CF to those cells.

Actually my data in this table is variable, it changes as soon as I change my branch therefore I needed CF for all the cells in this table.

Anyways, Thanks a million Khalid Took your a lot of time..

 

[Khalid NGO]

Phew...!, you got me right now.

As soon you mentioned in this post, I just tried converting the data of K5 to positive figure, it doesn't get highlighted. Hence proved you didn't apply CF to those cells.

Actually my data in this table is variable, it changes as soon as I change my branch therefore I needed CF for all the cells in this table.

Anyways, Thanks a million Khalid Took your a lot of time..

No problem dear, I enjoyed solving and learned from your question.

Regards,

 

[shaikhrulez]

No problem dear, I enjoyed solving and learned from your question.

Regards,

Thanks for your concern & interest