i have attached an excel file , in that i want the names for the particular date where value corresponding to the particular date is greater than 4.
For examples , i should get names :x,q,s for date 22/02/16
i have attached an excel file , in that i want the names for the particular date where value corresponding to the particular date is greater than 4.
For examples , i should get names :x,q,s for date 22/02/16
Hi Gaurav
In A13 type date 22/02/16 in B13 type 4 then in A14 copy paste below then hold control and shift together and then hit enter to make it array formula
drag downFormula:Please Login or Register to view this content.
array formulas are ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
CAN YOU PLEASE EXPLAIN
how in this formula, second last ROW function and last ROWS function is working
means how it is executing steps.
actually i have to do that in large data , i should know how this happens
Would this work for you?
Enter where you want the results. I copied the dates into another area and entered this formula under the first date and filled across and down to give the names that had values >4 for each date.
Enter this ARRAY formula (Ctrl + Shift + Enter)
Formula:Please Login or Register to view this content.
The absolute reference to the range in column A assures that the reference to column A remains constant when the formula is filled across and down.
INDEX is the range that contains the values to be returned by the rest of the formula.
SMALL has IF to define the range of values to be considered. The IF statement IF(B$3:B$10>4 will only consider values in B3:B10 that are greater than 4. The TRUE part of the IF statement ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1 gives the values {1;2;3;4;5;6;7;8} which are the rows being evaluated for being greater than 4. When taken as a complete statement IF(B$3:B$10>4,ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1) is understood by the SMALL function that the IF is in as {FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE}. All the FALSEs are the rows that have values that are <4. The numbers are the rows that have values >4.
ROWS($1:1) is the K factor for the SMALL function and will increase by 1 for each row filled down. This means that the smallest numbered row with a value >4 will be returned, the second row will have the next smallest row value containing a value >4 etc.
The whole formula is surrounded by and IFERROR which eliminates showing error values where there isn't data to be returned.
I J K L M N 2 22/02/2016 23/02/2016 24/02/2016 25/02/2016 26/02/2016 27/02/2016 3 x z x z v z 4 q x d x d x 5 s d q v q s 6 7
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
@ gaurav
=IFERROR(INDEX($A$3:$A$10,SMALL(IF(INDEX($B$3:$G$10,0,MATCH($A$13,$B$2:$G$2,0))>$B$13,ROW($A$3:$A$10)-ROW($A$3)+1),ROWS($A$1:A1))),"")
Bold part is first construction that brings all numbers in form of array for matched date, then those array of numbers are checked whether greater than number available in B13 or not, if greater then for that number, Row number is returned and those which are not False is returned.
here the construction Row($A$3:$A$10)-Row($A$3)+1 gives the serial numbers from if function as below
{FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE} as for attached file these are the row numbers where criteria is matched
Now small function picks 1 st smallest value from array and gives 3 which becomes row number for index function
Rows($A$1:A1) generates serial numbers as 1,2,3,4,5,6 and so on as you drag your formula down that becomes nth factor for small function
For further insight click the cell with formula, go to formula tab and hit formula evaluation to check how things are taking place.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks