excel conditional formatting AND isnumber
16,136
Solution 1
Based on your above screenshot you can do this
Solution 2
You simply use AND
in wrong way - arguments should be listed separated by commas:
=AND(INDIRECT(ADDRESS(ROW(),COLUMN()-1))>INDIRECT(ADDRESS(ROW(),COLUMN())),ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))))
Related videos on Youtube
Author by
Jakob
Updated on September 18, 2022Comments
-
Jakob almost 2 years
I'm trying to get my format sentence to work, but it just won't
I want to format the cell green if the cell is greater than the cell before it (and that cell contains a number)
I'm trying:
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))>INDIRECT(ADDRESS(ROW(),COLUMN())) AND ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))
I added the and because some cells with text next before them got red. It works without the AND ISNumber part
-
Siddharth Rout over 11 yearsWhy not something like this?
=AND(ISNUMBER($A$1),$A$2>$A$1)
This will format the CellA2
green if cellA1
is a number andA2>A1
-
Siddharth Rout over 11 yearsI'll assume that you missed my comment by mistake?
-
Jakob over 11 years@SiddharthRout I'm sorry, I don't see how that's different from Peter's suggestiion, only the arguments are switched, and the values are absolute and not dynamic
-
Siddharth Rout over 11 yearsI was assuming (based on your question) that there was only one cell? I see your screenshot now and understood that there is a dynamic range involved :)
-
Siddharth Rout over 11 yearsSee my answer below. If you remove the "$" then it becomes dynamic.
-
Jakob over 11 yearsWill it apply to all the cells in a pivot table even if the table expands beyond the range I have selected to start with?
-
Siddharth Rout over 11 yearsMay I see how your pivot looks so that I can test it first before commenting?
-
Siddharth Rout over 11 yearsOk after testing it on several pivots, I realized that the "Applies To" in conditional formatting remains absolute. So when the pivot rows expand all you have to do is to use the format painter to simply copy the CF to the rest of the cells.
-
-
Jakob over 11 yearscan you think of any reason the formatting would not be applied if they are numbers? I changed it and got no error, but not the aprropriate formatting either
-
Peter L. over 11 yearsIf entered in the cell, this formula gives a
circular reference
error. Perhaps Excel logic is a bit different than ours) anyway, could you please share manually colored sample - i think there's more simple way to conditionally color it? -
Jakob over 11 yearsthe problem is that it is applied to a pivottable, so it really has to be conditional and non-absolute, because the contents can change, but I'll see if I can get something shareable
-
Peter L. over 11 yearsJust color the sample - only relative cells position matters
-
Siddharth Rout over 11 yearsComing from the formula king, I take it as a huge compliment, barry :)
-
Peter L. over 11 years+1 with no doubts @Sid! Your answers with such nice screens make me weep often, since my team newcomers don't get the clue that 1 simple pic is 100000 times better than 1000 long explanation)))
-
Jakob over 11 yearsAnd it actually will work in the pivot table if you just tick the "apply to all data fields"