Banding Results
Hi,
I've tried to search to find an answer to this question, but I've drawn a blank so far. If I've got a column which displays salary details, is there any easy way to "band" the results into user-defined ranges (i.e. $0-$5000, $5000-$10000 etc.). Thus far I've been using a long-winded IF statement, but I'm hampered by the amount of nested IFs I can use - for the latest spreadsheet I'd need more than 20 IFs to split the results into the relevant bands. I can't help thinking there must be an easier way of doing this. Can anyone help? BH |
Hi
do your ranges always consist of +5000 steps? -- Regards Frank Kabel Frankfurt, Germany "Ben Hur" schrieb im Newsbeitrag om... Hi, I've tried to search to find an answer to this question, but I've drawn a blank so far. If I've got a column which displays salary details, is there any easy way to "band" the results into user-defined ranges (i.e. $0-$5000, $5000-$10000 etc.). Thus far I've been using a long-winded IF statement, but I'm hampered by the amount of nested IFs I can use - for the latest spreadsheet I'd need more than 20 IFs to split the results into the relevant bands. I can't help thinking there must be an easier way of doing this. Can anyone help? BH |
Maybe you could use a helper column that only returns the highest value of the
range: 0,5000,10000... =CEILING(A1,5000) And drag down: Or if you need both: =TEXT(CEILING(A1,5000)-4999,"$#,##0")&"-"&TEXT(CEILING(A1,5000),"$#,##0") (5000 went in 1-5000 not 5000-10000) Ben Hur wrote: Hi, I've tried to search to find an answer to this question, but I've drawn a blank so far. If I've got a column which displays salary details, is there any easy way to "band" the results into user-defined ranges (i.e. $0-$5000, $5000-$10000 etc.). Thus far I've been using a long-winded IF statement, but I'm hampered by the amount of nested IFs I can use - for the latest spreadsheet I'd need more than 20 IFs to split the results into the relevant bands. I can't help thinking there must be an easier way of doing this. Can anyone help? BH -- Dave Peterson |
All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com