Sep 28 2023 04:12 AM
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
Sep 28 2023 05:08 AM
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:
#,##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)
Sep 28 2023 05:39 AM
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
Sep 28 2023 07:38 AM
Sep 28 2023 10:36 PM
Sep 29 2023 12:05 AM
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:
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:
-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)