# Thread: Format the 3 numbers in a column closest to ZERO Thanks: 0 Likes:  1 Post #5305891 (1)

1. ## Re: Format the 3 numbers in a column closest to ZERO

Using a negative number seems to mess it up cuz I have tried several different ways and several different format techniques.

2. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
Can you help me fix it so it will only HL -10 and below. When I say below I mean -11, -12 etc..
Has the object of CF changed. This seems to be implying that you only want to highlight numbers below -9. Is that the case?

In the earlier posts you were wanting to highlight the numbers closest to 0 or closest to 50 no matter whether the numbers were above or below those values.
Can you clarify?

As with the earlier tasks, some sample data and expected results would help to clarify.

3. ## Re: Format the 3 numbers in a column closest to ZERO

Yes that is correct ... I am serving another function separate from the other one. I'm still good with that one.
I want to HL all numbers below -9

Thanks Pete!

4. ## Re: Format the 3 numbers in a column closest to ZERO

Here is a column of sample data.
Column V8 to V14 specifically.

 -12% -33% -7% -22% -4% -18% -4%

5. ## Re: Format the 3 numbers in a column closest to ZERO

Originally Posted by Ultimate Selector
I want to HL all numbers below ...
All numbers or just the closest 3 below?

Originally Posted by Ultimate Selector
... below -9
Below -9 or below -9%?

6. ## Re: Format the 3 numbers in a column closest to ZERO

-9%

All numbers that are -9 and below.
If there is one then just one. But if there are say 3 that are below -9 or lower then all three of those numbers.

7. ## Re: Format the 3 numbers in a column closest to ZERO

Then this is a very different scenario to the previous one and a very simple CF formula should do it for you.

CF below -9%

 V 8 -12% 9 -33% 10 -7% 11 -22% 12 -4% 13 -18% 14 -4%

Conditional formatting
 Cell Nr.: / Condition Format V8 1. / Formula is =V8<-0.09 Abc

Excel tables to the web >> Excel Jeanie HTML 4

8. ## Re: Format the 3 numbers in a column closest to ZERO

Well once again you earned your keep Pete!
I was using a whole number of -9 instead of the -0.09 in my CF formula.

I knew it had to be something simple but I would have never thought it was that.
U Da Man! Many Thanks Pete!

Have a terrific Morning!
-U.S.

9. ## Re: Format the 3 numbers in a column closest to ZERO

Hi Pete,

Hope all is well ...

I have one more issue for you to resolve if you could please.

I am trying to add up the net value of these individual rows.

Then I want to also add up just the positive numbers by themselves for each row.

Then I want to also add up just the negative numbers by themselves for each row.

The issue is that these are all in % (percentages). I can't seem to tackle this one. I know it's gotta be simple tho.

Here is my sample data for you.

 5% -11% -6% 3% -8% -5% -27% -36% -63% 4% -12% -8%

Thanks Pete!

Regards,
U.S.

10. ## Re: Format the 3 numbers in a column closest to ZERO

Like this? Each formula copied across.

% Calculations

 A B C D 1 2 5% -11% -6% 3 3% -8% -5% 4 -27% -36% -63% 5 4% -12% -8% 6 Net -15% -67% -82% 7 Positive 12% 0% 0% 8 Negative -27% -67% -82%

 Cell Formula B6 =SUM(B2:B5) B7 =SUMIF(B2:B5,">0") B8 =SUMIF(B2:B5,"<0")

Excel tables to the web >> Excel Jeanie HTML 4