If a cell is between two numbers then it equals...

[travistea]

Hey guys, I have two sheets, one with fillable data and the other is the spreadsheet with the results of the data.

My spreadsheets are for a hypothetical business, and have 10 years of info in the results sheet. They vary greatly by the figures I put into the fillable data sheet.

I have projections for ten years of profits and want to add a tax cell for each year . See Below:

if taxable income is over	but not over	tax is	plus	%	of amount over
$0	$17,850	$0.00	+	10.00%	$0.00
$17,850	$72,500	$1,785.00	+	15.00%	$17,850

and it goes on for a few more brackets.

I would like add a self generating figure to see how much taxes I would pay in a tax cell using the info from profits and tax rate cells.

Any ideas?

Thanks guys

[MrShorty]

I think I would put the tax information into a lookup table and use a VLOOKUP (or similar) formula to extract the tax information. Help file http://office.microsoft.com/en-us/ex...011.aspx?CTT=1 pay particular attention to the description of the 4th/range_lookup argument. If your lookup table is sorted with the lookup value in ascending order, VLOOKUP() should be able to return the correct value.

[travistea]

Alright, I got it sorted out thanks to Shorty. I used the LOOKUP formula. This is how it turned out:

=LOOKUP(K19,$B$16:$F$24)+(K19-LOOKUP(K19,$B$16:$C$24))*LOOKUP(K19,$B$16:$E$24)

Thanks for the help!!!