Forum Discussion
Formula help
Here is a simple IF formula that makes it possible.
=IF(B2="Cancelled","0",B2)
Also attached is an example file.
I would be happy to know if I could help.
NikolinoDE
I know I don't know anything (Socrates)
NikolinoDE Thank you but not quite right My sheeet looks like this.
- Please more details, in which cell is Sales Value?
only see the amount (I don't know whether this is called a sales value or a deposit).
Remember that the same cell with value entry and formula is not possible, if ... then with other options such as VBA code.
Please provide step-by-step information for your project.- Thanks for the reply. The column with Dollar valye is the sales column the other column is blank with a drop down box where the only option is "cancelled". What I was wanting to do, was that upon choosing CANCELLED the coresponding sales value will turn to 0, This is so that the running total will reflect the fact that this sale is no longer happening and therefore sales total needs to be reduced.
Thanks againAs NikolinoDE points out, Excel does not allow formulas to alter input data or calculated data in other cells. That means that the input sales amount will always take the value inserted by the user.
What can be done, however, is to reformulate expressions that reference the amount so that they also test the cancellation status. Thus the amounts and SUM of amounts could read
= IF(Status="CANCELLED", 0, Amount) = SUMIFS(Amount, Status,"")respectively.
Something else you can do to move towards your intended goal is to use conditional formatting to alter how the amount is displayed. You could make the cancelled amounts show as zero but I would be reluctant to display a non-zero amount as a zero; sooner or later, you or someone else will get caught out by that. What I would recommend is a greyed-out display of the original number with strikethrough.
