Excel's formatcells/Number/Custom >1 or =0

[drom]

Hi and Thanks in advance!

For this eg I can't use the normal conditional formatting, not VBA

I have a huge SumProduct formula on a cell (No problem)
If the result Shows 0, I Am going to put "Wrong"

I can do this using excel's formatcells/Number/Custom like:

• ;;"Wrong";

Now I would like to put when the formula shows >1 "Wrong" as well

kind of:

• [>1] "Wrong";;"Wrong";

I could use the formula on other cell and use a easy formula on the activecell like:

• If(OR(B2=0,B2>1),"Wrong","")

But is it possible using excel's formatcells/Number/Custom

 

[SanjayGMusafir]

You must be using Excel 2021. I recommend you to show only current Excel version and platform in your profile.

As of your problem, with Excel 2021 better use LET Function like

=LET(x,Your entire formula, If(OR(X=0,X>1),"Wrong",x))

It shall solve your problem. Check this and revert.

 

[drom]

Yes I am using 2021, anyway Iwas trying to know if was possible to use
Excel's formatcells/Number/Custom

Unfortunatelly this job goes for different users and for sure some of them are not using 2021, 365

 

[SanjayGMusafir]

anyway Iwas trying to know if was possible to use
Excel's formatcells/Number/Custom

Not sure if it works the way you want it to do (as per) OP.