Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Variance-- Positive Negative Display

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    California
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    California
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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
    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    California
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  7. #7
    Guest

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"
    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •