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

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?

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

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(A20,A2<1)

Hope this helps.

Pete

On Feb 5, 10:32*pm, Sue in AZ
wrote:
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?

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

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, - FormatCellsNumber tabPERCENTAGE

Then use this formula:

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

Then, your conditional formatting is:

Formula Is: =AND(A20,A2<1)

--
Biff
Microsoft Excel MVP


"Sue in AZ" wrote in message
...
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?

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

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" wrote in message
...
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?

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

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" wrote:

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?

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

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

"Tyro" wrote:

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" wrote in message
...
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?