1

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?

1

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.

1

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 |
+--------+--------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.