LinkBack URL
About LinkBacks
HI all,
I have searched and tried some functions with no success. I have a table with random numbers and I want to search a number between 0.480 and 0.410 and finally give the number if possible the minimum.
Thank you for your help
HELP.xlsx
Search in table a number with conditions
HI all,
I have searched and tried some functions with no success. I have a table with random numbers and I want to search a number between 0.480 and 0.410 and finally give the number if possible the minimum.
Thank you for your help
HELP.xlsx
Hi XCel_Novice,
Do you just want to know which cells are between 0.480 and 0.410 maybe by highlighting them or do you want actually extract those number somewhere?
Thanks,
Fonzie
Hi Fonzie29
I want to extract them to an empty table
thx
XCEL_NOVICE,
Welcome to the forum!
Attached is a modified version of your posted workbook.
In cell J3 is the minimum number to search for (0.410)
In cell J4 is the maximum number to search for (0.480)
In cell J7 and copied down to J27 is this array formula. Note that array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter (that's how it gets surrounded by the curly braces {}, do not try to add those manually).
=IF(OR(COUNT($J$3:$J$4)<2,ROWS(J$6:J6)>COUNTIFS($B$3:$G$27,">="&$J$3,$B$3:$G$27,"<="&$J$4)),"",SMALL(IF($B$3:$G$27>=$J$3,IF($B$3:$G$27<=$J$4,$B$3:$G$27)),ROWS(J$6:J6)))
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
@ Tigeravatar
the ''ROWS(J$6:J6)'' is to specify where the value will appear??
As a side note, it would be easier to do this if all of the numbers were in a single column, then you could just do a number filter for numbers between x and y.
Thank you Tigeravatar, I will look at it and get back to you
Re: Search in table a number with conditions
You can try this formula XCEL_NOVICE if you want to keep the formatting of your table and just display the values you need in another table with the values you want.
Thanks,
Fonzie
The first time, it is used to verify that there is still data to be displayed, the second time it is used to return the results in ascending order (smallest to largest) in conjunction with the SMALL() function.
Re: Search in table a number with conditions
HELP3.xlsx
I Tried but it doesn't work, i went with your suggestion and put the table on a
single row it gives 0 and nothing in the rest. I guess I'm doing something wrong, don't know what
Ok, this looks completely different than your original workbook. What are you trying to do?? I think you can replace the SMALL function with MIN, and remove the ROWS(D$3:D3), and you also need to confirm the formula with Ctrl+Shift+Enter so that it gets surrounded by the curly braces and becomes calculated like an array formula:
=IF(OR(COUNT(B3:C3)<2,ROWS($D$3:D3)>COUNTIFS($B$21:$AH$21,">="&C3,$B$21:$AH$21,"<="&B3)),"",MIN(IF($B$21:$AH$21>=C3,IF($B$21:$AH$21<=B3,$B$21:$AH$21))))
For a non-array (regular formula) version, we can do this:
Note that the regular version will only work if your data only consists of positive numbers. See attached for both versions.=IF(OR(COUNT(B3:C3)<2,COUNTIFS($B$21:$AH$21,">="&C3,$B$21:$AH$21,"<="&B3)=0),"",SMALL(INDEX(($B$21:$AH$21>=C3)*($B$21:$AH$21<=B3)*$B$21:$AH$21,),COUNTIF($B$21:$AH$21,"<"&C3)+COUNTIF($B$21:$AH$21,">"&B3)+1))
Thank you very much it's a success. Have a nice one
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks