+ Reply to Thread
Results 1 to 3 of 3

I'm a newbie please help with my function

  1. #1
    Registered User
    Join Date
    08-09-2005
    Posts
    1

    I'm a newbie please help with my function

    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. #2
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Quote 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.
    Last edited by BenjieLop; 08-09-2005 at 04:38 PM.
    BenjieLop
    Houston, TX

  3. #3
    Bob Phillips
    Guest

    Re: I'm a newbie please help with my function

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

    --

    HTH

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


    "colincannon" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    > View this thread: http://www.excelforum.com/showthread...hreadid=394335
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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