+ Reply to Thread
Results 1 to 13 of 13

If number is Positive display in D20, if Number is negative display in D19

  1. #1
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    If number is Positive display in D20, if Number is negative display in D19

    Hi guys wondered if anyone could help me, I'm trying to create a completion statement on where I will add up the money my client has sent to me and also add up the money I need to pay out in order for them to complete their transaction. The end result I want is I would like to display whether my client has given me to much money and I need to repay them. Or if they haven't given me enough then I need to invoice them. I need excel to display for me after all the figures have been added and subtracted if the end figure is negative then display in D19 (balanced owed to you). If the end figure is positive (Balance required from you) display in D20.

    Sorry if I have explained it in a confusing way! Any help will be appreciated

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: If number is Positive display in D20, if Number is negative display in D19

    You will need to put a formula in both D19 and D20, using an IF Statement, so if(total>received,"Invoice more","") and if(total<received,"Invoice too much","")
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If number is Positive display in D20, if Number is negative display in D19

    I need excel to display for me after all the figures have been added and subtracted
    Ok, where's that figure located?

    Let's assume it's in cell A1.

    Enter this formula in D19:

    =IF(A1<0,A1,"")

    Enter this formula in D20:

    =IF(A1>=0,A1,"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: If number is Positive display in D20, if Number is negative display in D19

    Quote Originally Posted by Tony Valko View Post
    Ok, where's that figure located?

    Let's assume it's in cell A1.

    Enter this formula in D19:

    =IF(A1<0,A1,"")

    Enter this formula in D20:

    =IF(A1>=0,A1,"")
    Thanks alot mate that worked. One more question (sorry lol) I also want excel to display the highest value out of column ''money in'' and column ''money out'' so say if cells C1:C15 are higher than B1:B15 (and the other way round) display in C18 and D18 how would I go about doing that?

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If number is Positive display in D20, if Number is negative display in D19

    Maybe these:

    =MAX(B1:B15)

    =MAX(C1:C15)

  6. #6
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: If number is Positive display in D20, if Number is negative display in D19

    Quote Originally Posted by Tony Valko View Post
    Maybe these:

    =MAX(B1:B15)

    =MAX(C1:C15)
    Would I put those formulas in the same cell? What I want to do is add up money in and money out and whatever figure is highest I want it to display on 2 specific cells. So if money in equals 10,000 and money out equals 15,000 Id want the figure of 15,000 to display In cell C21 and D21.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If number is Positive display in D20, if Number is negative display in D19

    One way...

    Data Range
    B
    C
    D
    1
    In
    Out
    2
    81
    86
    3
    54
    42
    4
    7
    97
    5
    96
    95
    6
    48
    93
    7
    19
    96
    8
    27
    27
    9
    88
    28
    10
    84
    43
    11
    12
    13
    14
    15
    ------
    ------
    ------
    16
    17
    18
    19
    20
    21
    607
    607


    This formula entered in C21 and copied to D21:

    =MAX(SUM(B2:B15),SUM(C2:C15))

  8. #8
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: If number is Positive display in D20, if Number is negative display in D19

    Thanks mate perfect. How would I make the negative symbol permanently removed in cell D19 as Ive tried the ones I know but they seem to cause problems with my other formulas.
    Last edited by arlu1201; 05-13-2014 at 08:18 AM. Reason: Removed whole post quote to avoid clutter.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If number is Positive display in D20, if Number is negative display in D19

    I don't understand.

    Is there a negative number in the cell?

    If so, why would you want to remove the negative symbol? Removing the symbol would not affect the true underlying value of the cell.

  10. #10
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: If number is Positive display in D20, if Number is negative display in D19

    In cell D19 do this with the formula:

    =(Whatever your formula is)*(-1)

  11. #11
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: If number is Positive display in D20, if Number is negative display in D19

    Tony can you have a look over here? Thanks!

    http://www.excelforum.com/excel-form...ml#post3694589

  12. #12
    Registered User
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: If number is Positive display in D20, if Number is negative display in D19

    Quote Originally Posted by Tony Valko View Post
    I don't understand.

    Is there a negative number in the cell?

    If so, why would you want to remove the negative symbol? Removing the symbol would not affect the true underlying value of the cell.
    After it has done all the calculations if there's a balance owed to my client it will show a positive number in C20 but if I require more money for them it shows the correct amount but with a negative symbol in D19. The formula works great I was just wondering if there was a way to remove the negative symbol as I don't really need it there. Its not a big problem though, thanks a lot for your help mate you've really helped me out here.

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If number is Positive display in D20, if Number is negative display in D19

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-07-2014, 04:08 AM
  2. Display formula value as a negative number
    By rhudgins in forum Excel General
    Replies: 10
    Last Post: 03-04-2011, 05:18 PM
  3. Formula to display difference as a positive number.
    By Ashone in forum Excel - New Users/Basics
    Replies: 16
    Last Post: 04-17-2010, 04:27 PM
  4. Graphs - How to display negative as positive
    By daisyoc in forum Excel General
    Replies: 3
    Last Post: 07-18-2009, 06:04 AM
  5. How do I change the display of a negative number to just read 0?
    By mfederan@peoplepc.com in forum Excel General
    Replies: 3
    Last Post: 04-18-2005, 07:38 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1