Excel formula that purposefully always makes cell $ zero ?

streets

New Member
Joined
Mar 23, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
For some financial analysis I do, in some excel cells, I desire that the output end-result always report as $ zero. It is purposefully desired, very intentionally, that certain cells equate to the amount $ 0.00, in the end, no matter what is in that cell for formula. A math equation in the cell is necessary; I can't leave the cells blank or just the actual number of " 0 ". This is so that reviewers can see there is work done and know that I consider the right amounts and where they come from.

There has been a previous solution, but it is tedious to do and it is neither efficient nor 100% effective, so that is why I am on this forum. For explain that, I get the feed-in of the excel and financial data given to me by a coworker. In the excel given to me, inside one of the excel cells, there has been this formula : =R21*R22 . I cannot delete what is already there; I must continue to have those remain. Using other data from other cells and other sources, that math equation for those two equals to an amount of (negative) -19.3 . The no-longer-desired way of how I've solved this problem, is just simply I add 19.3 to it, so the formula becomes =R21*R22+19.3 . It is then $ zero. However, sometimes, after I do that, I make further other adjustments that affect R21 and/or R22. After those other adjustments having been made, it turns-out that there is an amount - something other than $ 0.00. This is infuriating because I had already made $ zero. Sometimes I forget then adjust it again, and I submit it wrongly. An amount there, any amount, is un-desired. So I ask for help on this forum, is there some sophisticated excel formula I can add to after the =R21*R22 part that will *always* make it $ zero?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If you simply want all negative calculations to return zero, and not the negative number, but leave any positive calculations alone, simply use this formula:
Excel Formula:
=MAX(R21*R22,0)

This simply tells Excel to return the larger (maximum) of 0 and your calculation.
 
Upvote 0
Thanks, but I also cannot have any positive amount. It is monthly work. Month-to-month, it swings either side of $ zero, depending upon however the financial data flow. Both positive and negative amounts are un-desired, so I need excel formula that always makes it $ zero in all cases.
 
Upvote 0
Thanks, but I also cannot have any positive amount. It is monthly work. Month-to-month, it swings either side of $ zero, depending upon however the financial data flow. Both positive and negative amounts are un-desired, so I need excel formula that always makes it $ zero in all cases.
I don't understand. If you always need 0, why not just enter 0?
There is no need for a formula then.
 
Upvote 0
The reviewer of my work must see that I am considering the appropriate cells. If I leave blank or enter 0 , then that means those cells that need to be considered are not considered.
 
Upvote 0
I'm not sure I understand, but why doesn't a simple
Excel Formula:
=0
work?
 
Upvote 0
Really don't see the point in this, but maybe
Excel Formula:
=if(R21*R22=0,0,0)
 
Upvote 0
Solution
Really don't see the point in this, but maybe
Excel Formula:
=if(R21*R22=0,0,0)
Yeah, I was going to suggest something like:
Excel Formula:
=R21*R22*0
or
Excel Formula:
=(R21*R22)-(R21*R22)

But anyone who understands basic Excel would be able to see that all of these formulas always evaluate to 0, so they really are not being considered after all.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top