If I have values in column A in a spreadsheet starting from A1 to A5 as for example 1, 2, 3.5, 4, 6 and I need to extract all values less than 5 in a new column, what is the best method? And how do I set the auto extraction if the value 5 is in for example in cell C1. That is mean when I changed the value in C1 the values extracted in the new column will changed according to the new value in C1.
You can try it:
insert your data e.g. in column A as you said. Then in front of each cell in column A (I mean column B) enter this function:
=if(A1
Thanks Ehsan, but I really need to make the new list containing only the required values without zeroes. I need to extract the exact values less than 5. however, I can do that by advanced filter but I am looking for an auto function - as I said - to work if I changed the target cell which is in my example C1
It may be best to enter all your data completely and then use the SORT option, sorting your data from lowest number to highest number (ascending order) for the variable you want to extract. Excel will automatically move all the other cell data along with the sort. Then, all of your data will be in rank order and you can extract the information you need very easily. It might work?
The easiest way is with IF. You can use the formula that Ehsan gave you but if you don't want any zeros, just replace 0 by "". It would give:
=if(A1
Thanks Jean, I think this might be the best way as I couldn't find better than this.
It may be late to answer this, but I want to add some trick to Jean-Nicolas Audet answer.
Usually when I want to clone some tables with certain circumstances (like the
"How can I extract values that are less than 10 from different values in column to be in a new column?"
First solution
In column C1 you write: if(A1
- Badges
- Science topic