Weird prob: Format 3rd condition for other than first two

[Sue in AZ]

Hello Excel gurus!

I have a cell (Say A1) that I'm trying to conditionally format when the
adjacent cell (say A2)is between but not equal to 0 and 100 percent (I'm
trying to show when something is in progress, but not completed). I don't
want it to do anything at 0% or 100%.

Possibly complicating the issue, the value in the adjacent cell (A2) is a
vlookup of a another cell (say A3), returning number provided in 0-100
format. I want to display 0%-100% format, so my formula in A2 is something
like
=(VLOOKUP(A3,$O:$Z,12,FALSE))&"%"

To do it, I've set up three conditions.
1. Format (to do nothing) when
Formula Is =A2=100&"%"
2. Format (to do nothing) when
Formula Is =A2=0&"%"

To do the third one (the one that really matters), I've been searching the
boards and can't find a way to do it gracefully.

I currently have:
3. Format special when
Formula Is =AND(VLOOKUP(A3,$O:$Z,12,FALSE)<"100")

My problem is that it's weird and akward to vlookup the same thing again
(but I couldn't resolve the added "%" otherwise), but more troubling issue is
that when the value is something greater than 100, it still formats it as set
in condition 3. It works when any number other than 0 and 100, not just
numbers other than them and less than 100. That's just not right....right?
Is my logic all weird?

Thanks for reading all the way through....any thoughts?

 

[Pete_UK]

You can avoid having the textual display of percentages by changing
your vlookup formula to:

=(VLOOKUP(A3,$O:$Z,12,FALSE))/100

and formatting the cell as percentage with 0dp.

So, your first two CF conditions would then be:

1: Formula Is: =A2>=1
2: Formula Is: =A2<=0

And your 3rd condition can be:

Formula Is: =AND(A2>0,A2<1)

Hope this helps.

Pete

 

[T. Valko]

returning number provided in 0-100 format.

I want to display 0%-100% format, so my formula in A2 is something like
=(VLOOKUP(A3,$O:$Z,12,FALSE))&"%"

Use the below formula and just format the cell as PERCENTAGE. As it is, your
formula is returning a TEXT string which is why you can't get the
conditional formatting to work.

So, format cell A2 as PERCENTAGE, - Format>Cells>Number tab>PERCENTAGE

Then use this formula:

=VLOOKUP(A3,$O:$Z,12,FALSE)/100

Then, your conditional formatting is:

Formula Is: =AND(A2>0,A2<1)

 

[Tyro]

Why don't you simply format A2 where the lookup formula is as percent? So if
it returns 100, that will display as 100%, 50 will display as 50% and 0 as
0%
Then you can format the cell with =AND(A2<>0,A2<>100) When use AND you must
specify at least 2 conditions. In your =AND(Vlookup .....) you have only 1.


Tyro

 

[Sue in AZ]

Thanks guys, that totally makes sense. I originally had everything set up
with the vlookup column formatted as percentage and the cell, but then the
data came from another program and C&P'ing it in made 100% go to 10000%. Your
vlookup/100 solution is so much more elegant than mine! Thanks!

And so is your conditional formula solution. Thanks!
You guys rock!

 

[Sue in AZ]

Aaaah, thanks....I need to go review that AND stuff.