Variance-- Positive Negative Display

[SquirrelGuirrel]

Currently I am using the VAR function to calculated a variance % between 2 numbers. What I want to do it show whether it was a positive or negative variance and have the negative variance show in parentheses or preferably show in red text. I think it's partially a formual issue and conditional formating. Also, my conditional formating box doesn't appear to have a colored text can this be done?

 

[Aladin Akyurek]

On 2002-02-27 12:56, SquirrelGuirrel wrote:
Currently I am using the VAR function to calculated a variance % between 2 numbers. What I want to do it show whether it was a positive or negative variance and have the negative variance show in parentheses or preferably show in red text. I think it's partially a formual issue and conditional formating. Also, my conditional formating box doesn't appear to have a colored text can this be done?

Try

Format|Cells

Choose Accounting on the Number tab.

Set Currency Symbol to None.

 

[SquirrelGuirrel]

Hi Aladin
It's me, Heidi from a few weeks ago. You are in the Netherlands, yes? I had to re-register for the new site so I have a new user name.
Here is my problem more clearly:
Last month This Month Variance
12.5% 10.0% 0.25%

So since this month sales were down 12%
I would like the varianc value to be negative 12% and red. I tried the currency thing and it didn't work Is there something I can do in my formulat to compare the variance to the original number to show that it is a decrease? Thank you

 

[Aladin Akyurek]

On 2002-02-27 14:33, SquirrelGuirrel wrote:
Hi Aladin
It's me, Heidi from a few weeks ago. You are in the Netherlands, yes? I had to re-register for the new site so I have a new user name.
Here is my problem more clearly:
Last month This Month Variance
12.5% 10.0% 0.25%

So since this month sales were down 12%
I would like the varianc value to be negative 12% and red. I tried the currency thing and it didn't work Is there something I can do in my formulat to compare the variance to the original number to show that it is a decrease? Thank you

Hi Heidi,

How about entered in C2

=(A2>B2)*-(the-formula-for-variance)+(A2<B2)*(the-formula-for-variance)

where A2 houses the last month figure and B2 this month figure.

You could then format C2 as Number and choose for Negative numbers the red colored number between parens.

If this does not work for you, please post the formula for variance (It would take too long to weed thr the Archives).

Aladin
This message was edited by Aladin Akyurek on 2002-02-27 16:21
This message was edited by Aladin Akyurek on 2002-02-27 16:24

 

[SquirrelGuirrel]

Here is the speadsheet section

A6 B6 C6
%This month PriorMonth% Variance
12.5% 10.0% 0.00000%

This is the function for variance:
=var(a6,b6)
This is the formula I entered:
=(A6>B6)*-VAR(A6,B6)+(A6>B6)*(VAR(A6,B6))

I got 0% as shown in C2, but there was a decrease of .25%

Hope you are having a nice day today

 

[Aladin Akyurek]

On 2002-02-27 15:57, SquirrelGuirrel wrote:
Here is the speadsheet section

A6 B6 C6
%This month PriorMonth% Variance
12.5% 10.0% 0.00000%

This is the function for variance:
=var(a6,b6)
This is the formula I entered:
=(A6>B6)*-VAR(A6,B6)+(A6>B6)*(VAR(A6,B6))

I got 0% as shown in C2, but there was a decrease of .25%

Hope you are having a nice day today

OK, I had a typo in the formula in my previous reply.

Why do you use VAR? And, how do you compute, using ordinary maths, a .25% decrease given

%This month = 12.5% and

PriorMonth% = 10.0%

Aladin

 

[Guest]

Sorry, the 25% was a typo that I didn't notice until I got home. I gues I don't need to use VAR I just tried it for the first time. Thank you for your help I wil try your suggestions. The board seems really busy these days. It's great, so helpful. We should be able to repay you in some way. If you ever need help with interior design let me know I know it's not rocket science but it's what I'm good at..
Thanks again, Heidi

 

[Chris Davison]

Also, my conditional formating box doesn't appear to have a colored text

that's a bit odd.

just to check :

format
conditional formatting
format
font tab

and bang in the middle is "Colour"