Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Stumped

  1. #1
    New Member Blackspear's Avatar
    Join Date
    Apr 2002
    Location
    Gold Coast, Queensland, Australia
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Last week I sold $110 (A1), this week I sold $77 (B1) the percentage increase/decrease (C1) is -30%, what formula do I need to place in C1?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default



    In C1 enter:

    =(B1-A1)/A1

    Format C1 as %.

  3. #3
    New Member Blackspear's Avatar
    Join Date
    Apr 2002
    Location
    Gold Coast, Queensland, Australia
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your reply, that doesn't work, it gives 43%, were the answer is a decrease of -30% in sales, $110 last week, this week down by 30% to $77

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =(B1/A1)-1

    Tom

    [ This Message was edited by: TsTom on 2002-04-27 04:22 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-27 04:00, Blackspear wrote:
    Thanks for your reply, that doesn't work, it gives 43%, were the answer is a decrease of -30% in sales, $110 last week, this week down by 30% to $77

    =(B1-A1)/A1 in C1 gives me -30%, when A1 houses 110 and B1 77.


  6. #6
    New Member Blackspear's Avatar
    Join Date
    Apr 2002
    Location
    Gold Coast, Queensland, Australia
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My appologies Aladin, I don't know what I did, but your formula works, thank you very much. I have one more question for you. I am calculating 100/10 = 10, if 100/0 = 0, how do I get it to display "0" rather than the error code "Num/0!" ???

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin has left the building...

    =IF(ISERROR(100/0),0,100/0)

    Tom

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-27 04:44, Blackspear wrote:
    My appologies Aladin, I don't know what I did, but your formula works, thank you very much. I have one more question for you. I am calculating 100/10 = 10, if 100/0 = 0, how do I get it to display "0" rather than the error code "Num/0!" ???
    If you want to avoid the #DIV/0! error, you can check the divisor as in:

    =IF(E2,E1/E2,0)

    When E1 houses 100 and E2 0 (or E2 is empty), this formula will return 0.

    The condition of the IF function in the above formula for any number other than 0 is TRUE, otherwise FALSE. When the condition evaluates to TRUE than the divison E1/E2 is performed, otherwise 0 is returned.

    Aladin

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
  •