How can prevent getting a "0" in the cell where I place this formula?

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I have this formula in multiple cells and every one of them has a "0". Ok I can turn all "0" off but that is not what I need. Can I prevent this?

Thank you for your time.

Excel Formula:
=IF(AX6>0;AY6-(AY6*AX6);AY6)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
i assume you get this when AY is blank or zero
what would you want if AY6 was a zero ?

=IF(AND(AX6>0,OR(AY6<>"",AY6>0));AY6-(AY6*AX6);AY6)

will still give a zero if AY6 contains a zero
 
Upvote 0
i assume you get this when AY is blank or zero
what would you want if AY6 was a zero ?

=IF(AND(AX6>0,OR(AY6<>"",AY6>0));AY6-(AY6*AX6);AY6)

will still give a zero if AY6 contains a zero

If AX6 is 1 then AY6-(AY6*AX6) will still result in 0, irrespective of the value in AY6
Am unsure why the brackets are there tbh!
Should just be

AY6-AY6*AX6
 
Upvote 0
This might be a slightly convoluted option, but I hate this as well, and there is a simple fix using conditional formatting :)

You can set up a rule that specifies it requires the cell value to be 0 (this will work even though the actual content may be a formula)
You can then set the formatting rules to just have the text colour the same as the background colour of your cell!

It is simpler than trying to add variables to your formula and can easily be adjusted to encompass all ranges you need it to :)
In my case it spans from cell D14 to cell J14

Good luck!
Your friendly Internet Simple_DSA
 

Attachments

  • cond.format.png
    cond.format.png
    45.5 KB · Views: 3
Upvote 0
i assume you get this when AY is blank or zero
what would you want if AY6 was a zero ?

=IF(AND(AX6>0,OR(AY6<>"",AY6>0));AY6-(AY6*AX6);AY6)

will still give a zero if AY6 contains a zero
AY6 will always be higher then zero, so that is not a problem.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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