PDA

View Full Version : Solved: Populate hyphen for continuous three or more zero in a row



sarat
10-03-2012, 10:31 AM
Hi
I have 60 columns in the attached sheet. In the 61st column, I want to display hyphen symbol (-) for the particular row if the row contain more than three zero (>=0) continuously.

In the attached excel, hyphen symbol should be displayed in column 'BP' for row 5, 10 and 16
Is there any formula. Plz help me.

p45cal
10-03-2012, 04:42 PM
BP2 copied down:
=IF(SUMPRODUCT(($F2:$BK2=0)*($G2:$BL2=0)*($H2:$BM2=0)) > 0,"-","")

sarat
10-03-2012, 09:33 PM
Thanks p45cal for your great help.
This formula also works for blank rows with no data. Is it possible to skip row (e.g. row number of 19 onwards in attached spreadsheet)

Also this formula works for rows where data present in all the cells that means If data are missing for few cells of a particular row, the hyphen symbol still appears (e.g. row number 18, col AT-BC)

p45cal
10-04-2012, 12:12 AM
B2:
=IF(SUMPRODUCT(($F2:$BK2=0)*($G2:$BL2=0)*($H2:$BM2=0)*ISNUMBER($F2:$BK2)*IS NUMBER($G2:$BL2)*ISNUMBER($H2:$BM2)) > 0,"-","")

sarat
10-06-2012, 06:20 AM
Thanks p45cal for your great help. I have checked in large number of datasets. Its working fine