Add a number based on list of criteria ??

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Example: Cell A = 50, Cell B = 96%, Cell C = Cell A + (Cell A x Cell
B) = 98

I want to be able to compare the list of criteria and add a number to
Cell C accordingly
For example: If Cell B is between 95 and 100%, Add 1 to the total in
Cell C
If between 85 and 94%, add 2... etc.

There are more than 7 so I know I can't simply nest If statements...
Any advice on how I could fairly simply accomplish this?

TIA!
 
One way:

Put your % bands in a table (say in J1:Kx, e.g.:

J K
1 0 11
2 5% 10
3 15% 9
....
10 85% 2
11 95% 1
12 100% 0

Then

C1: =(1+B1)*A1+VLOOKUP(B1,J1:K12,2,TRUE)
 
Would this do it?

=A1+(A1*B1)+IF(B1<44%,7,IF(B1<54%,6,IF(B1<64%,5,IF(B1<74%,4,IF(B1<84%,3,IF(B
1<94%,2,1))))))
 
Back
Top