Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-17-2020, 12:42 AM
HussainAlqatari HussainAlqatari is offline NEED HELP!: Excel tricky problem Windows 10 NEED HELP!: Excel tricky problem Office 2007
Novice
NEED HELP!: Excel tricky problem
 
Join Date: Jun 2020
Posts: 5
HussainAlqatari is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 06-19-2020, 11:54 AM
xor xor is offline NEED HELP!: Excel tricky problem Windows 10 NEED HELP!: Excel tricky problem Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

See the attached.
Attached Files
File Type: xlsx Tricky.xlsx (12.4 KB, 9 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
NEED HELP!: Excel tricky problem Tricky Mail Merge Labels fairchance Mail Merge 2 11-18-2015 06:17 AM
NEED HELP!: Excel tricky problem Tricky wildcard search NobodysPerfect Word 10 03-19-2014 04:29 AM
NEED HELP!: Excel tricky problem Tricky bookmark and bullet question skroser Word 1 07-09-2013 08:28 AM
Tricky IF formula twolfend Excel 1 12-05-2011 09:02 AM
NEED HELP!: Excel tricky problem Tricky Word problems Eva Word 3 10-14-2011 03:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft