Formula to populate symbol if zero repeated continiously for particular row

[ashokbioinfo]

Hi

I have 60 columns. in the 61th column, I want to display bar symbol (-) if the cell for each row contain zero continuously.


Is there any formula. Plz help me.


for row2 , suppose the column ap,aq and ar contain zero and rest cell contain other value. so for that particular row, i want to populate bar symbol (-) in the 61th column

 

[Luke M]

Is this 2 different questions, or the same one? I'm not sure if you're talking about rows or columns. If the question is"Create a formula to display a bar if all the cells in previous 60 columns contain 0" then formula is:

=IF(COUNTIF(A2:BH2,0)=COLUMNS(A2:BH2),"-","")


If I've understood you incorrectly, can you help clarify?

 

[SirJB7]

@Luke M

Hi!

As far as I could see the user wants to display a dash on 61th column (BI) if at least 2 consecutive cells in a row have a zero value. Without helper columns I wasn't able to find out a solution yet.

Regards!

 

[Luke M]

Thanks SirJB7. Assuming your interpretation is correct, formula would be:

=IF(SUMPRODUCT((A2:BG2=0)*(B2:BH2=0)),"-","")


PS. Hitting the beers early today? "61th"? <grin>

 

[SirJB7]

@Luke M

Hi!

It seems to have been vokda + amarula, I guess...

61th! It's an horror. I publicly apologize for my mistake.

Regards!

PS: Everybody knows that it's 61rd.

 

[bobhc]

Good evening SirJB7


Was not aware you spoke with a lisp, 61th, 61rd a few more Carlsberg's and 61fs will trip of your tongue