# Solved: 2003 Excel Negative numbers

Discussion in 'Business Applications' started by SusanB27, May 14, 2010.

Not open for further replies.

Joined:
May 14, 2010
Messages:
4
First time posting...I am working on a spreadsheet for work in calculating a benefit. I have listed in C13 a minimum benefit (currently \$25, but it varies). Later in the spreadsheet I subtract two numbers (c32-c33). If the number is less than zero, I have the formula put 0 in C34 as the answer IF(C32-C33<0,"0",(C32-C33)). That works fine. The problem is that later I when I want to list out what to pay, if the final answer is less than the minimum, I want the minimum to show, otherwise I want the calculated answer to show. Here is my formula. IF(C34<C13,C13,C47) Everything works great unless the original numbers subtracted is less that zero....for example \$600 - \$600 - the final results spits out the minimum of \$25. Perfect. But when \$600 - \$601 = really equals -1, but I have it showing 0, the final answer spits out 0, not the \$25 that I want. Any ideas on what to do to get it to put the C13 value instead of 0? Thanks so much for the assistance!

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
could you say - if C34=0 , then c13 other wise do you calc using a nested if
so
IF(C34=0,C13, IF(C34<C13,C13,C47))

or I may have mis read the post

Joined:
May 14, 2010
Messages:
4
I tried that and got the same issue. At exactly 0 it works fine and spits out the minimum value of c13, but when c34 is a negative (even though it is showing 0) it does not put the c13 value, but puts 0. Is their some isse with Excel on negative numbers affecting formulas?

4. ### loserOlimbs

Joined:
Jun 19, 2004
Messages:
7,800
Why not use:
=IF(C32-C33<25, "25",C32-C33)

On step to get C33-C32, and it also shows you the pay, unless its under the minimum, in which caes it shows the min. \$25.

Joined:
May 14, 2010
Messages:
4
That would be great, but for each claim, the minimum could be different...that is why I have to reference the c13 cell - which is where the user enters the correct minimum for that specific claim.

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
well C34 should be 0 - however, in your first post, if you are putting "0" then thats text and not a number change it to just 0
also change to "less than or equal"
see below
IF(C32-C33<=0,0,(C32-C33))

otherwise can you post a sample spreadsheet -
manage attachements and upload from there

7. ### loserOlimbs

Joined:
Jun 19, 2004
Messages:
7,800
=IF(C32-C33<C13, C13,C32-C33)

Hows that then. Does the same but from the c13 value

Joined:
May 14, 2010
Messages:
4
Thanks to everyone...I looked back and found I had a "0" at one point. When I changed it to plain old 0, it now works! While I would like 0 to display versus just a blank with a line, I can live with that. Thank you so much!!! You finished my week off with a smile!

9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
you have a format set to make the line - its a common financial format.
Look at the format of the cells

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
you may also want to look at my post #6 above

As Seen On