Forum Discussion
If Formula
I am creating a bonus spreadsheet and cannot figure out how to do this formula in excel. The part of the formula I am having difficulty with is the either or aspect. I have a formula that calculates based on profit alone but does not take into account the Bps part. Below is the bonus grid I am working with and an example to help illustrate what I am attempting to do. If you can please help me out I would truly appreciate it. I have been attempting to figure this out with no luck. This is the formula I have so far.
=IF(F22<=5000000,IF(AND(L22>=5000,L22<10000),0.0005*F22,IF(L22>=10000,0.0015*F22,IF(AND(L22>(-5000),L22<=2500),0*F22,IF(AND(L22<=(-5000),L22>(-10000)),(-0.0005)*F22,IF(L22<=(-10000),(-0.0015)*F22))))))
Bonus Grid:
If Volume is Less than $5,000,000
+20 Bps (.002) or $10,000 profit | 15 Bps (.0015) | Explanation: If branch has a profit of $10,000 or has a profit that is greater than (.0020)*(volume) than they are paid a bonus of (.0015)*(volume) |
+10 Bps (.001) or $5,000 profit | 5 Bps (.0005) | Explanation: If branch has a profit of $5,000 or has a profit that is greater than (.0010)*(volume) than they are paid a bonus of (.0005)*(volume) |
$2,500 profit to ($4,999) loss | 0 Bps | Explanation: Between $2,500 profit and $4,999 loss |
-10 Bps or ($5,000) loss, greater of | -5 Bps (-.0005) | Explanation: Branch has a loss of $5,000 or has a loss of (.0010)*(volume); the greater of the two. If this is true they produce a loss of (.0005)*(volume) |
-20 Bps or ($10,000) loss, greater of | -15 Bps (-.0015) | Explanation: Branch has a loss of $10,000 or has a loss of (.0020)*(volume); the greater of the two. If this is true they produce a loss of (.0015)*(volume) |
Branch A
Total Volume: $1,379,921
Branch Profit: $3,678.00
Total volume (1,379,921) * 20 Bps (.002) = 2,759.84
The branch has a profit greater than $2,759.84 so they will be paid 15 Bps (.0015) * volume (1,379,921) = $2,069.88
1 Reply
Please provide more examples to understand the calculation.
