Hi All,
I have this formula with conditional formatting, and it works as expected. =and(sumproduct(--(isna(b2:d2)))=0,or(b2>0,c2>0,d2>0)), it works when cells b2..d2 are blank.
I am trying to understand this formula and was hoping it can be explained in plain english. I understand in general sumproduct takes two arrays, and if they are true then multiplies them as 1's and 0's and if = 1, then it takes values and adds them (hence sum of product).
What I need help in is understanding the arrays in this formula and how it works with sumproduct.
I understand that isna(b2:d2), if it is true then values in b2:d2 are not #N/A, however not clear how to interpret when = 0 , (isna(b2:d2)))=0
So going back to formula,= and(sumproduct(--(isna(b2:d2)))=0,or(b2>0,c2>0,d2>0)),
if the isna range (b2:d2) is true that it is not #N/A, because we are forcing to = 0, , then per sumproduct when value is true, it is considered a 1, now we multiply this with the 2nd half of sumproduct array, or(b2>0,c2>0,d2>0) , when any of these conditions are met i.e. b2>0, or c2>0, or d2 >0, and when these conditions are met it is also considered a 1. so 1 from first array x 1 from 2nd array= 1, so then make the cell the defined color per conditional formatting rule. is this correct interpretation?
Bookmarks