NEED HELP URGENTLY: Excel tricky problem

HussainAlqatari

New Member
Joined
Jun 17, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
Hello everyone,

I am using EXCEL 2007, 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:

qLQSwCz.png


OUTPUTS:

CdVFPZz.png


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
010.png


Can one use those function, or others but not vba, 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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,685
Welcome to MrExcel!

I don't think you can do this with a single formula. I've tried several versions, and everything I've tried requires using a MAX function (or equivalent) on a single row of a 2 dimensional array, which can't be done. This is the best I've come up with using a helper column:

Book1
ABCDE
1201195
220101211
310302011
45110020
5111789
62050
7540
8891
Sheet3
Cell Formulas
RangeFormula
E1:E5E1=MAX(IF($A$1:$A$8<=SMALL($C$1:$C$5,ROW()),$A$1:$A$8))
B1:B8B1=ISNUMBER(MATCH(A1,$E$1:$E$5,0))+0
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


The E1:E5 column finds the highest value in A:A less than the nth smallest value in C:C. Then the B formula just sees if the value in A exists in E. I'll continue to ponder this a bit more, but this may be the best I can do.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,565
Office Version
  1. 365
Platform
  1. Windows
With a single formula. I've only done a quick test with the example provided,
Book1
ABC
120119
2201012
3103020
451100
51117
62050
7540
8891
Sheet5
Cell Formulas
RangeFormula
B1:B8B1=--OR(A1=SMALL($A$1:$A$8,COUNTIF($A$1:$A$8,"<="&SMALL($C$1:$C$5,ROW($C$1:$C$5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,080
@Eric W Re: " using a MAX function (or equivalent) on a single row of a 2 dimensional array "

If the column argument of INDEX is 0, it returns the single row specified by the Row argument

=MAX(INDEX($A$1:$G$10, 3, 0)) returns the max of A3:G3
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,685

ADVERTISEMENT

Sorry, Mike, I was a bit too sweeping in my statement. Yes, that is a possibility for a single row. But in the formulas I was experimenting with, I'd need to find the max for every row in the array, something like:

=MAX(INDEX(A1:G10,{1,2,3,4,5,6,7,8,9,10},0))

and using INDEX in array functions is problematical. And that particular construct was only one element of a larger formula. So I did consider something like that, but could not get it to work. Which is not to say that there's a way that I did not think of. In fact, jasonb75 has come up with an interesting approach that I hadn't even thought of.
 

HussainAlqatari

New Member
Joined
Jun 17, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
With a single formula. I've only done a quick test with the example provided,
Book1
ABC
120119
2201012
3103020
451100
51117
62050
7540
8891
Sheet5
Cell Formulas
RangeFormula
B1:B8B1=--OR(A1=SMALL($A$1:$A$8,COUNTIF($A$1:$A$8,"<="&SMALL($C$1:$C$5,ROW($C$1:$C$5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dear, it shows the results as 1's From B1 to B8

What I did:

First, copy: =--OR(A1=SMALL($A$1:$A$8,COUNTIF($A$1:$A$8,"<="&SMALL($C$1:$C$5,ROW($C$1:$C$5)))))

Second, select B1:B8.

Third, paste.

Fourth, CTRL+SHIFT+ENTER

Any thing wrong?
 

HussainAlqatari

New Member
Joined
Jun 17, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
With a single formula. I've only done a quick test with the example provided,
Book1
ABC
120119
2201012
3103020
451100
51117
62050
7540
8891
Sheet5
Cell Formulas
RangeFormula
B1:B8B1=--OR(A1=SMALL($A$1:$A$8,COUNTIF($A$1:$A$8,"<="&SMALL($C$1:$C$5,ROW($C$1:$C$5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.
1592455651359.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,565
Office Version
  1. 365
Platform
  1. Windows
What I did:

First, copy: =--OR(A1=SMALL($A$1:$A$8,COUNTIF($A$1:$A$8,"<="&SMALL($C$1:$C$5,ROW($C$1:$C$5)))))

Second, select B1:B8.

Third, paste.

Fourth, CTRL+SHIFT+ENTER
Only paste the formula into B1, then use the fill handle to copy down after doing Ctrl Shift Enter.
 

Forum statistics

Threads
1,148,277
Messages
5,745,807
Members
423,978
Latest member
leodo21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top