Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .