I am looking for a formula that can lookup a number in cell A1, compare to cells A10:A15, figure out which range cell A1 falls into and multiply 2 other cells together depending on what range A1 is in.
example:
Below are numbers that are in excel spreadsheet:
CELL A10 1-10 20%
CELL A11 11-20 30%
CELL A12 21-30 40%
CELL A13 31-40 50%
CELL A14 41-50 60%
Cell A1 = 15
Cell B1 = 100
I am trying to write formula that says look at Cell A1, then look at cell A11:A15, and if less than info in legend then multiply Cell A1 by the % above. I thought formula would be:
=IF(A1<A11,A1*B1,IF(A1<A12,A1*B1) and so on but not working.
Or a formula that says look at A1 compared to A10:A15, find the range that A! falls into and multiply A1 by the % next to the correct range.
Either will work...thank you in advance
