Result should be always zero

[iLja_bmgs]

Hello everyone

Surfed lots of posts, but didn't found decision

Question:

If my calculation show -1 but i need to show only 0, how can i add this value to expences and show it as Expences 4 with formula?

Same for positive result

Of course i can do it mannualy but i have lot of results and its not worth to spend hours for this correction, especially when numbers are decimal

Thanks in advance

[#2]

Hi @iLja_bmgs,

the solution to make the result of a calculation in Excel always zero is to use the following formula:

=MAX(0, A1-B1)

Where A1 and B1 are the cells containing the values that you want to subtract.

This formula will return the maximum value of 0 and the result of the subtraction. Since the maximum value of 0 and any other number is always 0, this formula will always return zero.

To use this formula to add a value to expenses and show it as Expenses 4, you would use the following formula:

=MAX(0, H2-H3-H4)+H4

This formula will return zero if the result of the subtraction is negative, otherwise it will return the result of the subtraction plus the value in cell H4.

You can also use a custom number format to hide zero values in Excel. To do this, follow these steps:

1. Select the cells that you want to apply the custom number format to.
2. Click the Format Cells button on the Home tab.
3. In the Format Cells dialog box, click the Custom category.
4. In the Type field, enter the following number format:

#,##0

This number format will display the value of the cell if it is greater than zero. If the value of the cell is zero, the cell will be blank.

To use the custom number format to hide zero values for the Expenses 4 column, you would select cells H5:H10 and then apply the custom number format as described above.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

@LeonPavesic 

Thank you for quick answer

I think ive asked not correctly so i will refrase

I need, Result (-1) to add in Expences 3 so that Expences 3 will become (5) and Result will be (0)

Same with positive Result

[#4]

Is the 6 being returned by a formula or is it a constant (keyed in)?

[#5]

@Patrick2788 

In real document formulas are everywhere, screen below:

[#6]

Hi @iLja_bmgs,

To add the negative value of Result to Expenses 3 and show it as Expenses 3, you can use the following formula:

=IF(Result<0,Expenses3+Result,Expenses3)

This formula will return the value of Expenses3 plus Result if Result is negative. Otherwise, it will return the value of Expenses3.

For example, if Expenses3 is 5 and Result is -1, then the formula will return 4. If Expenses3 is 5 and Result is 1, then the formula will return 5.

To use this formula, enter it in cell H3, which is the cell where you want to display the updated value of Expenses 3. Then, copy the formula down to the other cells in the Expenses 3 column.

The following table shows how the formula will work for different values of Expenses3 and Result:

Expenses3 Result Formula Result

5	-1	4
5	0	5
5	1	5
-5	-1	-6
-5	0	-5
-5	1	-4

You can also use the following formula to show Result as 0 if it is negative:

=IF(Result<0,0,Result)

To use this formula, enter it in cell H4, which is the cell where you want to display the updated value of Result. Then, copy the formula down to the other cells in the Result column.

The following table shows how the formula will work for different values of Result:

Result Formula Result

-1	0
0	0
1	1
-10	0
10	10

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)