Forum Discussion
Find and Replace Range
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.
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).
- I had thought about nesting if statements but the values I am working with are 1-100 being grouped in ranges of 10 which would mean 10 if statements and my understanding is that the max is 7. Would this produce the same problem? Thanks
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.
