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

1. ## 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. ## 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","")

3. ## 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,"")

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

Originally Posted by Tony Valko
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?

Maybe these:

=MAX(B1:B15)

=MAX(C1:C15)

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

Originally Posted by Tony Valko
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. ## 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. ## 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.

9. ## 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. ## Re: If number is Positive display in D20, if Number is negative display in D19

In cell D19 do this with the formula:

11. ## 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. ## Re: If number is Positive display in D20, if Number is negative display in D19

Originally Posted by Tony Valko
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. ## Re: If number is Positive display in D20, if Number is negative display in D19

You're welcome. Thanks for the feedback!

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

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

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