Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

enter image description here

share|improve this question
    
Why not something like this? =AND(ISNUMBER($A$1),$A$2>$A$1) This will format the Cell A2 green if cell A1 is a number and A2>A1 –  Siddharth Rout Feb 18 '13 at 17:36
    
I'll assume that you missed my comment by mistake? –  Siddharth Rout Feb 18 '13 at 18:03
    
@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 –  Jakob Feb 18 '13 at 18:05
    
I 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 Feb 18 '13 at 18:08
    
SiddharthRout - that's definitely what I want, only with dynamic cells –  Jakob Feb 18 '13 at 18:31
show 4 more comments

2 Answers 2

up vote 3 down vote accepted

Based on your above screenshot you can do this

enter image description here

share|improve this answer
1  
+1, Good answer Sid –  barry houdini Feb 18 '13 at 19:07
    
Coming from the formula king, I take it as a huge compliment, barry :) –  Siddharth Rout Feb 18 '13 at 19:09
    
+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))) –  Peter L. Feb 18 '13 at 19:30
    
And it actually will work in the pivot table if you just tick the "apply to all data fields" –  Jakob Feb 18 '13 at 19:38
add comment

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

share|improve this answer
    
can 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 –  Jakob Feb 18 '13 at 17:30
    
If 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? –  Peter L. Feb 18 '13 at 17:49
    
the 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 –  Jakob Feb 18 '13 at 17:53
    
Just color the sample - only relative cells position matters –  Peter L. Feb 18 '13 at 17:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.