Gary Thornton
New member
How do I suppress zero and negative results in my spreadsheet?
excel worksheet
- Thread starter Gary Thornton
- Start date
Hello Gary Thornton
To suppress zero and negative results in your spreadsheet, you can use an IF formula like this:
=IF(A1<=0, "", A1)
This formula checks if the value is zero or negative; if it is, the cell is left blank (""); otherwise, it displays the original value. You can apply this logic wherever needed. Let me know if you're using a specific function or chart so I can tailor the solution!
To suppress zero and negative results in your spreadsheet, you can use an IF formula like this:
=IF(A1<=0, "", A1)
This formula checks if the value is zero or negative; if it is, the cell is left blank (""); otherwise, it displays the original value. You can apply this logic wherever needed. Let me know if you're using a specific function or chart so I can tailor the solution!
Gary Thornton
New member
Hello Shamima Sultana and thank you for your response to my query.
The formulas in my spreadsheet that are giving me negative and/or a visible zero (0), ie not a blank cell, results are:
=IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50) and
=IF($G$12>6,0,($F$12-$G$12))
in which Column F represents my 'Base' or minimum quantity, and Column G represents my Stock on Hand.
How and where in my formulas would I insert your formula to prevent these results?
Thank you again.
Gary Thornton
The formulas in my spreadsheet that are giving me negative and/or a visible zero (0), ie not a blank cell, results are:
=IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50) and
=IF($G$12>6,0,($F$12-$G$12))
in which Column F represents my 'Base' or minimum quantity, and Column G represents my Stock on Hand.
How and where in my formulas would I insert your formula to prevent these results?
Thank you again.
Gary Thornton
Hello Gary Thornton,
Thank you for the follow-up and for sharing your formulas!
You can nest your logic inside an additional IF that checks the final outcome to suppress zero or negative results. Here's how you can modify your formulas:
Your Formula:
=IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50)
Updated formula to suppress 0 or negative:
=IF(IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50)<=0,"",IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50))
Your Formula:
=IF($G$12>6,0,($F$12-$G$12))
Updated formula to suppress 0 or negative:
=IF(IF($G$12>6,0,($F$12-$G$12))<=0,"",IF($G$12>6,0,($F$12-$G$12)))
This structure keeps your original logic but hides the result if it ends up being zero or negative. Let me know if you'd prefer to display a custom message instead of leaving the cell blank!
Thank you for the follow-up and for sharing your formulas!
You can nest your logic inside an additional IF that checks the final outcome to suppress zero or negative results. Here's how you can modify your formulas:
Your Formula:
=IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50)
Updated formula to suppress 0 or negative:
=IF(IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50)<=0,"",IF($G$10>200,0,CEILING.MATH($F$10-$G$10,50)/50))
Your Formula:
=IF($G$12>6,0,($F$12-$G$12))
Updated formula to suppress 0 or negative:
=IF(IF($G$12>6,0,($F$12-$G$12))<=0,"",IF($G$12>6,0,($F$12-$G$12)))
This structure keeps your original logic but hides the result if it ends up being zero or negative. Let me know if you'd prefer to display a custom message instead of leaving the cell blank!
Gary Thornton
New member
Gary Thornton
New member
Hello Shamima Sultana
Thank you for your assistance, it is much appreciated. I will now sit down and apply your adjustments to the rest of my spreadsheet, allowing for different base quantities and other bits and pieces.
Best Regards,
Gary Thornton
Thank you for your assistance, it is much appreciated. I will now sit down and apply your adjustments to the rest of my spreadsheet, allowing for different base quantities and other bits and pieces.
Best Regards,
Gary Thornton