Excel Formula Help - Microsoft Community

i created formula percentage calculations , works pretty well. allows me negative percent changes , positive percent changes nicely , gets rid of dividing 0 problems:

=if(b2>=c2,((b2-c2)/(b2)),if(c2>b2,(-(c2-b2)/(c2))))

however, when both b2 , c2 0, gives me #div/0! error. want "null" when both numbers 0 because 0 0 means particular task rate on 0-6 scale not affecting them pre or post treatment. keep track of how many times particular task in fact "null".

also, how take average of entire column "null" in column, there formula that?

thanks!

trap error.

=iferror(if(b2>=c2,((b2-c2)/(b2)),if(c2>b2,(-(c2-b2)/(c2)))),"null")

you can run macro add trap selected range of cells.

sub iferroradd()
    dim r range
    each r in selection.specialcells(xlcelltypeformulas)
        r.formula = "=iferror(" & mid(r.formula, 2) & ",""null"")"
    next r
end sub

average ignores cells null

gord

Office / Excel / Microsoft Office Programming / Office 2016