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
Then the formula in B1 would be simply
The resulting columns A and B would look like
And if you want B1 to calculate the amount of markup instead of just stating the percentage, change the formula to