Count how many cells are 10% or more greater

[esseispercipi4]

Hello,

I have Excel 2007
I have two columns of data:

Booked Amount Actual Amount
10 11
100 95
20 14
50 64
100 120

I need an equation that will tell me how many cells in Column B (Actual) exceed the corresponding value in Column A (Booked) by 10% or more - the answer in this case is 3.

I cannot add extra columns as intermediate steps and the number of rows is dynamic over time.

My current solution would be to add a whole new tab to house the two columns and a third column that would check each row individually, returning a 1 or 0, and then do a SUM on that column and hide the tab. But I would like to avoid doing so and have it all in one equation.


Thanks!!!

 

[BiocideJ]

This formula should do the trick.
=SUMPRODUCT((B:B>=A:A*1.1)*ISNUMBER(A:A))

Ideally, you should specifically define the range, but if it is going to grow dynamically that could be difficult.

 

[jasonb75]

This formula should do the trick.
=SUMPRODUCT((B:B>=A:A*1.1)*ISNUMBER(A:A))

Ideally, you should specifically define the range, but if it is going to grow dynamically that could be difficult.

That will return #VALUE! error if there are text headers in the column.

This will work dynamically, commom practice is to use dynamic named ranges, but with a single formula I don't see any benefit.

=SUMPRODUCT(--($A$1:INDEX($A:$A,MATCH(1E+100,$B:$B))<$B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))),--ISNUMBER($B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))))

 

[BiocideJ]

That will return #VALUE! error if there are text headers in the column.

Good catch. For some reason I expected the ISNUMBER(A:A) to deal with that.

Alternatively, you could use this:
=SUMPRODUCT(ISNUMBER(A2:A65536)*(B2:B65536>=A2:A65536*1.1))
however, this isn't exactly dynamic anymore.

 

[esseispercipi4]

Thank you!! I got it working
My data is in the middle of a spreadsheet so when I entered my range, I just left the headers row out so they didn't cause an error.
I still got an error message when there were blank rows included in the range, but since part of the process is to delete extra rows, that problem fixes itself.

 

[BiocideJ]

This will work dynamically, commom practice is to use dynamic named ranges, but with a single formula I don't see any benefit.

=SUMPRODUCT(--($A$1:INDEX($A:$A,MATCH(1E+100,$B:$B))<$B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))),--ISNUMBER($B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))))

Just an FYI, this counts how many are larger and not how many are larger by 10%. I'm not sure which formula you ultimately used so if you used this one please follow-up since I'm not sure how to amend this formula. It's a little beyond me.

 

[jasonb75]

Good catch. For some reason I expected the ISNUMBER(A:A) to deal with that.

When you use functions such as isnumber or isna within sumproduct, you need to use double unary arrays, otherwise the logical test becomes redundant and the error will still pass to the result.


As you pointed out, I forgot to check the true difference in the values

=SUMPRODUCT(--($A$1:INDEX($A:$A,MATCH(1E+100,$B:$B))<($B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))/1.1)),--ISNUMBER($B$1:INDEX($B:$B,MATCH(1E+100,$B:$B))))

 

[esseispercipi4]

I used your initial one, =SUMPRODUCT((B:B>=A:A*1.1)*ISNUMBER(A:A)).

The one thing I just realized is that the second column can also say 'Closed' and those are being counted as 10%+ greater. I'm working on modifying the equation now to ignore those.

 

[jasonb75]

Realised my corrected formula falls foul to the same error that I pointed out in BiocideJ's suggestion

This method works, using 2 named ranges to make the formula dynamic, you will need to define these as shown in the bottom table.

Excel Workbook
	A	B	C	D
1	Booked	Actual
2	10	11
3	100	95		3
4	20	14
5	50	64
6	100	120
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[Aladin Akyurek]

Hello,

I have Excel 2007
I have two columns of data:

Booked Amount Actual Amount
10 11
100 95
20 14
50 64
100 120

I need an equation that will tell me how many cells in Column B (Actual) exceed the corresponding value in Column A (Booked) by 10% or more - the answer in this case is 3.

I cannot add extra columns as intermediate steps and the number of rows is dynamic over time.

My current solution would be to add a whole new tab to house the two columns and a third column that would check each row individually, returning a 1 or 0, and then do a SUM on that column and hide the tab. But I would like to avoid doing so and have it all in one equation.


Thanks!!!

Since you are on Excel 2007, you can set up a table using the Insert | Table option, which gives you a dynamic organization:

<TABLE style="WIDTH: 148pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=198><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=70> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>10%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 14.4pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl66 height=19>Booked</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" id=td_post_3111111 class=xl66>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl66>Eval</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68 height=19 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl68 height=19 align=right>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>95</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl68 height=19 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl68 height=19 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>64</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 height=19 align=right>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>120</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>1</TD></TR></TBODY></TABLE>

C1: 10%

A2:C7 is set up as a table.

C3, just enter:

=([@Actual]>=[@Booked]*(1+$C$1))+0

which is immediately applied (copied down) for all records by Excel's Table functionality.

Of course

=SUM(Table2[Eval])

gives you the count you need.

If the foregoing is not admissible or less desirable...

Let Sheet1 house A2:B7.

Define Lrow by means of Formulas | Name Manager as referring to:

=MATCH(9.99E+307,Sheet1!$A:$A)

which allows you to invoke:

=SUMPRODUCT(($B$3:INDEX(B:B,Lrow)>=($A$3:INDEX(A:A,Lrow)*(1+$C$1)))+0)

for the desired count.