If I had a cell that I wanted a multi possibility answer, how would I code it? Example:
I want info to display in A1, that if B1 is less than 5 display “X”, but if greater than 5 but less than 10- multiply by 2, greater than 10 but less than 20- multiply by 3, if greater than 20 but less than 30- multiply by 4….
any advice?
Something like this?
=IFS(A1<100,0,A1≤200,A1×2%,A1≤300,A1×3%,A1≤400,A1×4%,A1≤500,A1×5%,A1≤600,6%,A1>600,7%)
You didn't say what the rate is for 501 to 600 sells, so I just assumed.
If your region uses , as the decimal separator, be sure to replace the , in the formula with ;
If you are on a tiered commission schedule where you receive 2% on the first 200 sells, 3% on the second 100 sells, etc. then the formula is more complicated. Post if you need that.
SG
Something like this?
=IFS(A1<100,0,A1≤200,A1×2%,A1≤300,A1×3%,A1≤400,A1×4%,A1≤500,A1×5%,A1≤600,6%,A1>600,7%)
You didn't say what the rate is for 501 to 600 sells, so I just assumed.
If your region uses , as the decimal separator, be sure to replace the , in the formula with ;
If you are on a tiered commission schedule where you receive 2% on the first 200 sells, 3% on the second 100 sells, etc. then the formula is more complicated. Post if you need that.
SG
I am going to assume that you want to display in cell A1 the value of B1 times 2, if 5 <= B1 < 10, B1 times 3, if 10 <= B1 < 20 etc.
If this is the case, use the functions IFS in cell A1 like so :
You can keep on adding other tests at the end of the function, e.g "B1 < 40, B1 * 5".
I appreciate your response, but there’s a step in there missing and that’s the part I’m having trouble understanding. I will tell you the real world problem VS an example. I’m trying to auto calculate
my bonus. A1 is the sum of all sells. In B1 I need it to display where I’m at according to my bonus chart. Chart looks like this:
0-99 sells don’t qualify for bonus
100-200 sells A1 multiplied by 2%
201-300 sells A1 multiplied by 3%
301-400 sells A1 multiplied by 4%
401-500 sells A1 multiplied by 5%
601 and more A1 multiplied by 6 %
hope this helps clarify, I’m pulling my hair out on this issue
Actually I think that may work. Thanks! I didn’t think about not actually having to tell the app “if less than”. I think I was trying to make it harder than I had to. I will let you know if that don’t work. I greatly appreciate you!
Apple numbers < > formula
