+ Reply to Thread
Results 1 to 5 of 5

Please help me with formulas to solve a 50 numbers problem

  1. #1
    Registered User
    Join Date
    08-28-2021
    Location
    london
    MS-Off Ver
    xp , 2000
    Posts
    13

    Please help me with formulas to solve a 50 numbers problem

    Hello !
    Could you please help me with the formulas required to solve the following conditions ?

    All possible numbers are from 1-70
    In the first yellow row can be inserted 20 different numbers. I inserted the numbers from 1-20. These 20 numbers will be excluded from the count. So now only 50 numbers remain in the problem.
    The following rows each contain 20 numbers.
    I need the RESULTS collumn to show how many numbers out of the 50 are in each row but excluding duplicates.
    So for example the first result in my attached example is 11. These 11 numbers also need to be excluded from the following count. The next result is 14 , these 14 numbers also need to be excluded from the following count etc.
    The last result will be 0 , and if I add the results I will get the total of 50.

    Can anybody please help ?
    Thank you very much in advance !

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: Please help me with formulas to solve a 50 numbers problem

    I think your sample sheet doesn't exactly match your description -- specifically, you said that you entered the numbers 1 to 20 in row 2, but you really only entered the numbers 1 to 19. That caused me some confusion at the beginning, but once I realized the mistake, I think I worked out something that will work for 19 or 21 or 20 numbers entered in row 2. Note that I am not a fan of big ugly megaformulas, so I used a block of helper cells on the way to getting the solution.

    1) As I looked at the description and the sample sheet, it occurred to me that we are basically counting how many entries there are between row 2 and the row above the current row and subtracting that value from the count of the entries in the current row. =COUNT(A3)-COUNT(A$2:A2) entered in a convenient upper left corner of our helper block (I entered in A26). Note the mix of relative and absolute references, then copy down as far as needed (to row 37 in my case) and across as far as needed (column BR in my case). The result is a matrix of counts where 0s and negative numbers indicate that the number either hasn't been encountered yet or has been encountered more than once. A value of 1 indicates that this is the first time the number has been encountered, and should therefore be included in the count in column BS.
    2) At this point, all that is needed is to count the 1s in each row. A simple COUNTIFS() works well for this =COUNTIFS(A26:BR26,1) copied down to BS37 (obviously, replace references to the location in the spreadsheet where you put the counting matrix).
    3) To check the total, you can enter =SUM(BS26:BS37) where you want the check sum value to be.

    Assuming I have understand the task correctly, something like that should work.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Please help me with formulas to solve a 50 numbers problem

    With your version of Excel, I had to add helper columns in BT:EK to make this possible - I couldn't think how to do it otherwise.

    See attached.

    WBD
    Office 365 on Windows 11, looking for rep!

  4. #4
    Registered User
    Join Date
    08-28-2021
    Location
    london
    MS-Off Ver
    xp , 2000
    Posts
    13

    Re: Please help me with formulas to solve a 50 numbers problem

    Thank you MrShorty !

  5. #5
    Registered User
    Join Date
    08-28-2021
    Location
    london
    MS-Off Ver
    xp , 2000
    Posts
    13

    Re: Please help me with formulas to solve a 50 numbers problem

    Thank you for your help WideBoyDixon !!!
    Your formulas work great !!!
    Thank you very much !!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can you solve that formulas?
    By erimncl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2022, 03:24 PM
  2. Replies: 4
    Last Post: 10-21-2020, 03:50 PM
  3. How to solve this problem?
    By Indra Rai in forum Excel General
    Replies: 2
    Last Post: 09-15-2013, 09:26 AM
  4. Replies: 0
    Last Post: 08-26-2011, 12:45 PM
  5. solve this sum problem
    By bobyjalali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 03:24 AM
  6. please solve the problem
    By somaraju in forum Excel General
    Replies: 1
    Last Post: 02-23-2006, 07:20 AM
  7. Can you solve my problem?
    By Keith Robinson in forum Excel General
    Replies: 2
    Last Post: 04-14-2005, 05:06 PM

Tags for this Thread

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