IF/AND Statement or conditional formatting

Microsoft Office excel 2007 home & stude...
October 18, 2010 at 17:19:14
Specs: Windows 7
I am using MS Excel 2007

Ok, here is the data...

column G
2%
3.2%
12%

ranges (is on a separate sheet and cells):
-3.050% 3.050% GREEN
-5.050% -3.050% YELLOW
3.050% 5.050% YELLOW
1000.000% -5.051% RED
5.051% 1000.000% RED

Here is the scenario...

I need Col H to look up the value in Col G and tell me the result (Green, yellow, red) based on the ranges.

How can I do that? Please help!

Thank you so much!


See More: IF/AND Statement or conditional formatting

Report •


#1
October 18, 2010 at 20:55:24
re: Here is the scenario...

Here are the problems:

First:

-3.050%        3.050%    GREEN
-5.050%       -3.050%    YELLOW
3.050%         5.050%    YELLOW
1000.000%     -5.051%    RED
5.051%      1000.000%    RED

-3.050% is Green in the first line, Yellow in the second
3.050% is Green in the first line, Yellow in the third

What happens if the values in Column G hit those exact values? They can't return 2 different colors, so what do you want them to return?

Second:

1000.000%   -5.051%    RED
5.051%    1000.000%    RED

That's really confusing since -5.051% to 1000% includes all of your other ranges. Wouldn't that make all of your values return Red? Are you sure you meant to use 1000%?

Finally, re: and tell me the result (Green, yellow, red)

Are looking for a colored cell in Column H or do you want the actual text "Green", "Yellow" or "Red" returned?


Report •

#2
October 19, 2010 at 08:45:14
1. I apologize... the decimal place for the % is limited in my scenario below. The way that they are placed in the excel sheet does not duplicate (I think)...

-3.049499999% 3.04949999% GREEN
-5.049499999% -3.050% YELLOW
3.050% 5.049499999% YELLOW
-1000.000% -5.051% RED
5.051% 1000.000% RED

Does that help to clarify?

2. I used 1000 because some numbers will be very different so it may (in some scenarios) be as high as 900 or so. However I can change this so that it isn't so large but I hope that the overall idea is visible and understood.

3. The result is to be both... a colored cell AND the actual text "GREEN", "YELLOW" "RED"

Thank you for your help!


Report •

#3
October 19, 2010 at 12:19:17
I still think there's a problem with your ranges.

Green goes down to -3.049499999
Yellow starts at -3.050

Where do -3.0495 through -3.04999999 belong?

e.g. -3.0497 doesn't seem to fit in any range.

The same goes for numbers in the -5.0495 through -5.04999999 range.

e.g. -5.0497 doesn't seem to fit in any range.

Will these numbers never occur?


Report •
Related Solutions


Ask Question