if statement for +/- a percentage
Results 1 to 5 of 5

Thread: if statement for +/- a percentage
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default if statement for +/- a percentage

    Hello, I'm trying to display a tick or cross in F if a variance (D-C) is less than or greater than C but i'm having a total mind blank. Should be easy but its not!
    I've got the wingdings side easily just the formula is stuffing me around

    So

    Num1 ...Num2 ...Variance.....Mark
    C.............D............E...........F
    600 ......600............0........Tick
    13 ........14 ............1........Cross
    312 ......308 ..........-4 ........Tick
    312 ......388 ..........86 .......Cross
    312 ......333...........21.........Tick
    Last edited by marshy3300; May 8th, 2019 at 12:10 AM.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,654
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: if statement for +/- a percentage

    if a variance (D-C) is less than or greater than C
    If that's the case....why is one of these a tick and the other a cross ???


    312 388 86 Cross
    312 333 21 Tick
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if statement for +/- a percentage

    86 (though my math sucks and should be 76) is greater than 10% of 312 so its a cross
    21 is within the +/- 10% mark

    Really bad day..... This is all based off +/- 10% variance from C
    Last edited by marshy3300; May 8th, 2019 at 12:21 AM.

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,654
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: if statement for +/- a percentage

    So this

    Code:
    =IF(ABS(E1)< ABS(C1*0.1),CHAR(252),CHAR(251))
    Last edited by Michael M; May 8th, 2019 at 12:26 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: if statement for +/- a percentage

    Thanks Michael,

    Looks like that only works if the variance is 0. Otherwise all the examples show wrong though the symbol does change over the course of the sheet
    *
    Amended - Wrong cell references... Works a treat
    Thanks heaps
    Last edited by marshy3300; May 8th, 2019 at 12:51 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •