Forum Discussion

Jenine Montalbano's avatar
Jenine Montalbano
Copper Contributor
Apr 05, 2018

help with excel formula, not sure which one - THOUGHT THIS WOULD HAVE BEEN AN EASY ONE

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

 

 

Resources