What formula do i use?!

[Foz massey]

Hi guys,

I'm doing a spreadsheet, and basically I need to find a formula that filters data into three sections

3 bands exist:
$0 - $20,000
$20,001 - $40,000
$40,000 +

For instance the first $20,000 made goes into the first band
the next $20,00 goes into the second band
and anything more than that goes into the 3rd band

what formula do I use if say someone made $30,000 and I need the first $20,000 to filter into the band 1 and the next $10,000 to be entered into band 2??

Any help would be greatly appreciated
Thank you

 

[AlanY]

try this

Excel 2012
	A	B	C	D
1	50000		Band 1	20000
2			Band 2	20000
3			Band 3	10000
Sheet1

Cell Formulas
Range	Formula
D1	=MIN(A1,20000)
D2	=IF(A1>20000,MIN(A1-20000,20000),"")
D3	=IF(A1>40000,MIN(A1-40000,A1),"")