NEED HELP!: Excel tricky problem


  1. Posts : 5
    Win10
       #1

    NEED HELP!: Excel tricky problem


    Hello everyone,

    I am using EXCEL, and having this problem, which I think, tricky but easy to understand:

    Column A and Column C both contain INPUTS (all are numbers).

    Column B should contain OUTPUTS. (Which is the problem).

    The problem in words: Look at Column A, Find the greatest number which is less than or equal to the least number in Column C. Once you find that, return 1 in Column B.

    Again, Look at Column A, Find the greatest number which is less than or equal to the second least number in Column C.
    Once you find that, return 1 in Column B.

    Again, Look at Column A, Find the greatest number which is less than or equal to the third least number in Column C.
    Once you find that, return 1 in Column B.

    And so on,

    Return 0 in Column B for other cells

    See the following example:

    INPUTS:



    OUTPUTS:



    The 1 beside 5 because 5 is the greatest number in Column A (which is less than or equal to 7, which is the smallest number in Column C)

    The 1 beside 11 because 11 is the greatest number in Column A (which is less than or equal to 12, which is the second smallest number in Column C)

    The 1 beside 11 because 11 is the greatest number in Column A (which is less than or equal to 19, which is the third smallest number in Column C) (This happened earlier with 12)

    The 1 beside 20 because 20 is the greatest number in Column A (which is less than or equal to 20, which is the fourth smallest number in Column C)

    The 1 beside 89 because 89 is the greatest number in Column A (which is less than or equal to 100, which is the fifth smallest number in Column C)

    -------------------------------

    * INPUTS should not be changed or sorted.

    * Column A has no duplicate numbers.

    * Column C has no duplicate numbers.

    * The OUTPUTS are typed manually for illustration.

    --------------------------------

    I tried to combine the following functions; AND(), IF(), COUNT(), SMALL() ,,, but did not work

    Can one use those function, or others, to solve this problem? I believe, but not sure, it is possible with only these functions.

    --------------------------------

    Sorry for my poor English (I am Arabic). If my way of explaining my problem is not clear, then please ask me to clarify more. If you understood it, but could not solve it, then it would be really appreciated if you re-phrase it, so other members may help me. THANKS IN ADVANCE!
      My Computer


  2. Posts : 93
    Windows 10 Home Version 21H2
       #2

    There is a Microsoft Office Forum at:

    Microsoft Office Forums - Word, Excel, Outlook, PowerPoint, Project

    There is a section on Excel. I have found that the experts there are very knowledgeable and have helped me solve tricky Excel issues in the past.
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 12:00.
Find Us




Windows 10 Forums