# Google spreadsheet cell outcome dependent on another cell’s range

Example: column A1 is \$35.00. I want column B1 to be a percentage markup value dependent on the value in A1 (i.e.: 75%). So if A1 is between \$1 and \$10, B1 = 75%; if between \$11 and \$20, B1 = 60% and so on.

How can I achieve this?

I believe you are looking for a nested if statement. Put this formula in B1 and then drag it down as far as needed...

=IF(AND(A1>1,A1<10), A1*.75,(IF(AND(A1>10,A1<20), A1*.60, (IF(AND(A1>20,A1<30), A1*.50, (IF(test, then_true, otherwise_value))))))

I just did the first 3 iterations. You can continue from there and set the values as needed. Make sure to match left and right parenthesis.

I hope this helps.

While using nested `IF` statements works, the resulting formulas can be lengthy, error-prone, difficult to change or debug. For a complex pricing scheme I would use `VLOOKUP`. Example: the columns D and E contain the pricing policy

``````+-------------------------+--------+
| If price is at least... | Markup |
+-------------------------+--------+
|                       1 | 75%    |
|                      11 | 60%    |
|                      21 | 50%    |
|                      31 | 45%    |
+-------------------------+--------+
``````

Then the formula in B1 would be simply `=VLOOKUP(A1,D\$2:E\$5,2)`, which can be extended to the rest of B column. Here, `VLOOKUP` works by locating the first entry of column D that is <= the value in column A, and returning the matching value from column E.

The resulting columns A and B would look like

``````+--------+-----+
| \$35.00 | 45% |
| \$20.34 | 60% |
| \$11.00 | 60% |
| \$10.99 | 75% |
| \$25.50 | 50% |
+--------+-----+
``````

And if you want B1 to calculate the amount of markup instead of just stating the percentage, change the formula to `=A1*VLOOKUP(A1,D\$2:E\$5,2)`

``````+--------+--------+
| \$35.00 | \$15.75 |
| \$20.34 | \$12.20 |
| \$11.00 | \$6.60  |
| \$10.99 | \$8.24  |
| \$25.50 | \$12.75 |
+--------+--------+
``````