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]

Actually, I think what ended up sending my formula into returning errors was this... A2:A65536*1.1 you can't multiple text by 1.1.

In my experience, you don't need to use the double unary operator as long as you multiple the two by each other. (i.e. TRUE*TRUE = 1)

Also, to Aladin's response the OP said he couldn't add any new columns otherwise that is a more clear solution.

Thankfully, the original poster was able to salvage something workable from all of this.

 

[BiocideJ]

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.

Ignoring text in column B would just be
=SUMPRODUCT((B:B>=A:A*1.1)*ISNUMBER(A:A)*ISNUMBER(B:B))

again, amending the range to exclude any potential text cells in column A.

 

[BiocideJ]

I hate to have posted three times in a row, but this formula should take into account all of the possibilities mentioned in this thread and accurate give a result.

=SUMPRODUCT((B:B>=IF(ISNUMBER(A:A),A:A*1.1,1E+100))*ISNUMBER(A:A)*ISNUMBER(B:B))