Variance-- Positive Negative Display

SquirrelGuirrel

New Member
Joined
Feb 26, 2002
Messages
10
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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:)
 
Upvote 0
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
 
Upvote 0
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:)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top