I have an excel spreadsheet that has 2 columns of numbers side by side. I want a formula that counts the number of times the number in column one are larger than the number that is right beside it in column two. After it counts it I want to display the total number that are larger in a seperate cell. Is this possible. I cruised around the forum and I did not see what I was looking for so any help would be appreciated.

2. Originally Posted by colincannon
I have an excel spreadsheet that has 2 columns of numbers side by side. I want a formula that counts the number of times the number in column one are larger than the number that is right beside it in column two. After it counts it I want to display the total number that are larger in a seperate cell. Is this possible. I cruised around the forum and I did not see what I was looking for so any help would be appreciated.
ASSUME for this exercise that your numbers are in Cells A2:A100 and Cells B2:B100.

Based on the above assumption, here is a very simple solution (I am sure that there is a more elegant solution out there).

In Cell C2, enter this formula (without the quotes) "=A2-B2" and copy down until Cell C100. We will call Column C (Cells C2 to C100) as a helper column.

In cell C1, enter this formula: =countif(C2:C100,">0") and this will count the "number of times the number in column one are larger than the number that is right beside it in column two."

To display the numbers, you can enter this formula in Cell D2

=if(C2>0,A2,"")

and copy down until Cell D100.

Hope this is what you are looking for.

Regards.

=SUMPRODUCT(--(A1:A100>B1:B100))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"colincannon" <colincannon.1tijjd_1123618019.9967@excelforum-nospam.com>
wrote in message
news:colincannon.1tijjd_1123618019.9967@excelforum-nospam.com...
>
> I have an excel spreadsheet that has 2 columns of numbers side by side.
> I want a formula that counts the number of times the number in column
> one are larger than the number that is right beside it in column two.
> After it counts it I want to display the total number that are larger
> in a seperate cell. Is this possible. I cruised around the forum and
> I did not see what I was looking for so any help would be appreciated.
>
>
> --
> colincannon
> ------------------------------------------------------------------------
> colincannon's Profile:

http://www.excelforum.com/member.php...o&userid=26105
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1