Comparing 0 value and blank cells

[northernfrog]

Hi, I have a worksheet that compares paired responses to a set of questions grouped in sections and calculates percentage agreement by section and overall.

Column B and C contain response values that may be 0, 1, 2, 3. Cells in either column may also be left blank.
Column D contains a formula that evaluates whether or not the paired responses match i.e. in D2 =IF(B2=C2,1,0). Column D values are summed by section and the totals used to calculate the percentage elsewhere on the same worksheet and also on a summary worksheet.

The problem is the formula doesn't serve the purpose when a pair consists of a cell containing a value of 0 and a blank cell as it evaluates to 1 (true). There needs to be something added so when this occurs 0 will be returned in D. I've tried creating nested formulas that use the original formula and also include:
If B or C is blank and B or C = 0 then D=0
Assign "" when a cell in B or C is blank
Use count to determine if cells in B and C contain data
None give me a result to satisfy all cases. Would someone please explain which if any of the ways above is the best approach in this situation or suggest alternatives I could try.

 

[pgc01]

Hi northernfrog
Welcome to the board

Try, for ex.:

=IF(AND(COUNT(B2,C2)=2,B2=C2),1,0)

 

[F&s anaylst]

hi There,

if you want different for blank cells (i.e. blank cells to return blank cell and zero cells to return zero cells use the below if not use pgc formula)

=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(OR(B2=0,C2=0),0,1))

 

[northernfrog]

PCG, thank-you for the welcome and thanks to both you and Kevin for responding. I've tried both formulas alone and in combination with the one I've been using and still not able to get the expected results for all cases.

In the sample both are placed alongside of mine in D. I know that for the formula I'm using it is evaluating correctly in D3, but it does not give me the desired response. A blank response and one with a 0 value don't agree. PCG, your formula in column F corrected my problem but cause 2 blanks to evaluate to 0 instead of 1.


Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 57px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"><COL style="WIDTH: 52px"><COL style="WIDTH: 56px"><COL style="WIDTH: 52px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 44px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">QuesNo</TD><TD style="FONT-WEIGHT: bold">Response1</TD><TD style="FONT-WEIGHT: bold">Response2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center" colSpan=3>Agreement
1 (True), 0 (False)

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: left">1.1a</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ff99cc"></TD><TD style="BACKGROUND-COLOR: #ff99cc; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: left">1.1b</TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ff99cc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ff99cc"></TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: left">1.1c</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ff99cc"></TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: left">1.1d</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: left">1.2a</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ff99cc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: left">1.2b</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: left">1.2c</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ff99cc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=IF(B2=C2,1,0)</TD></TR><TR><TD>E2</TD><TD>=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(OR(B2=0,C2=0),1,0))</TD></TR><TR><TD>F2</TD><TD>=IF(AND(COUNT(B2,C2)=2,B2=C2),1,0)</TD></TR><TR><TD>D3</TD><TD>=IF(B3=C3,1,0)</TD></TR><TR><TD>E3</TD><TD>=IF(OR(ISBLANK(B3),ISBLANK(C3)),"",IF(OR(B3=0,C3=0),1,0))</TD></TR><TR><TD>F3</TD><TD>=IF(AND(COUNT(B3,C3)=2,B3=C3),1,0)</TD></TR><TR><TD>D4</TD><TD>=IF(B4=C4,1,0)</TD></TR><TR><TD>E4</TD><TD>=IF(OR(ISBLANK(B4),ISBLANK(C4)),"",IF(OR(B4=0,C4=0),1,0))</TD></TR><TR><TD>F4</TD><TD>=IF(AND(COUNT(B4,C4)=2,B4=C4),1,0)</TD></TR><TR><TD>D5</TD><TD>=IF(B5=C5,1,0)</TD></TR><TR><TD>E5</TD><TD>=IF(OR(ISBLANK(B5),ISBLANK(C5)),"",IF(OR(B5=0,C5=0),1,0))</TD></TR><TR><TD>F5</TD><TD>=IF(AND(COUNT(B5,C5)=2,B5=C5),1,0)</TD></TR><TR><TD>D6</TD><TD>=IF(B6=C6,1,0)</TD></TR><TR><TD>E6</TD><TD>=IF(OR(ISBLANK(B6),ISBLANK(C6)),"",IF(OR(B6=0,C6=0),1,0))</TD></TR><TR><TD>F6</TD><TD>=IF(AND(COUNT(B6,C6)=2,B6=C6),1,0)</TD></TR><TR><TD>D7</TD><TD>=IF(B7=C7,1,0)</TD></TR><TR><TD>E7</TD><TD>=IF(OR(ISBLANK(B7),ISBLANK(C7)),"",IF(OR(B7=0,C7=0),1,0))</TD></TR><TR><TD>F7</TD><TD>=IF(AND(COUNT(B7,C7)=2,B7=C7),1,0)</TD></TR><TR><TD>D8</TD><TD>=IF(B8=C8,1,0)</TD></TR><TR><TD>E8</TD><TD>=IF(OR(ISBLANK(B8),ISBLANK(C8)),"",IF(OR(B8=0,C8=0),1,0))</TD></TR><TR><TD>F8</TD><TD>=IF(AND(COUNT(B8,C8)=2,B8=C8),1,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[F&s anaylst]

hi there,

could you put the correct results into column D, i'm trying to work out have you are looking for exactly (just finishing of 20 hr shift at work so brain kinda dead now lol)

thanks
kevin

 

[mikerickson]

Instead of B2=C2, you could use EXACT(B2,C2)

 

[F&s anaylst]

hi there try the below think i got my head around what you are looking

=IF(AND(ISBLANK(B2),ISBLANK(C2)),1,IF(AND(COUNT(B2,C2)=2,B2=C2),1,0))

 

[northernfrog]

F&s, That works perfectly! Many thanks for taking the time. The count part was what I wasn't getting as according to Excel help it ignores empty cells so was trying things like IF(COUNT(B2:C2)<2 etc...

 

[bosco_yip]

A simplied formula from F&s anaylst

=(B2="")*(C2="")+(COUNT(B2,C2)=2)*(B2=C2)

Regards
Bosco