Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping Charts Displaying Zero Results | Charts and Charting in Excel |