Jul 13 2020 09:43 PM - edited Jul 13 2020 09:51 PM
Is it possible to use the find and replace feature to find a specific range of numbers between 0-100 in multiple rows/columns cells and replace it with a range value? For example if i had various numbers ranging from 0 to 100 in multiple cells, could i use find to find all numbers between 0 and 10 and replace the cell value with the range of 0-10? If not i would be open to simple workarounds to accomplish the same result.
Jul 14 2020 09:12 AM
@sirk78 How about creating another column with a formula that does it for you? For example you data is in column A and you put this in col B (starting at row 1):
=IFERROR(IF(AND(A1>=1,A1<=10),"1 - 10",A1),"")
if you have multiple columns like A:F then put it in G and copy down and right to L.
If you don't want the extra columns you can then cut and paste VALUES back over the orig data or maybe better yet is just HIDE those original columns (highlight A:F and right click and select hide).
Jul 14 2020 11:02 AM
Jul 14 2020 11:38 AM
Solution@sirk78 you can use the IFS() statement:
=IFS(A1<=10,"1-10",A1<=20,"11-20",A1<=30,"21-30",A1<=40,"31-40", ... , A1<100,"91-100")
or try this:
=MROUND(A1+4.99,10)-9 & " - " & MROUND(A1+4.99,10)
which will work even past 100 but does assume 0.01 threshold (i.e. 10.001 will still fall under 1-10 instead of 11-20), but you can tweak that as you wish.
Jul 14 2020 12:46 PM
The IFS worked perfect, that was a new one for me. Still so much to learn! Thanks for your time!
Jul 14 2020 11:38 AM
Solution@sirk78 you can use the IFS() statement:
=IFS(A1<=10,"1-10",A1<=20,"11-20",A1<=30,"21-30",A1<=40,"31-40", ... , A1<100,"91-100")
or try this:
=MROUND(A1+4.99,10)-9 & " - " & MROUND(A1+4.99,10)
which will work even past 100 but does assume 0.01 threshold (i.e. 10.001 will still fall under 1-10 instead of 11-20), but you can tweak that as you wish.