How to show fold changes in an excel spreadsheet when there are 0 values in the denominator. In the example below P30 is the numerator and P2 is the denominator. The logic is:
Both numerator and denominator = 0 → output 0.00
Denominator = 0, numerator ≠ 0 → output >{numerator} (2 decimals)
Numerator = 0, denominator ≠ 0 → output <{denominator} (2 decimals)
Otherwise → output normal division with 2 decimals.
=IF(AND(P30=0,P2=0),"0.00",
IF(P2=0,">"&TEXT(P30,"0.00"),
IF(P30=0,"<"&TEXT(P2,"0.00"),
TEXT(P30/P2,"0.00"))))