Daniel_Fdrvc
New Member
- Joined
- Jan 17, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello all,
i'm struggling with one task. The point is, that i have simple calculation of sales: we sell the same amount of contracts every month, which increases the overall portfolio. But now I need to insert a flag that will tell me in which month the contract drop will occur. We have two scenarios: 1. if drops 1 of 10 contracts (10% or in other words, every tenth contract) and 2. if drops 3 of 10 contracts (30%). I have created some calculations that shows me the FIRST DROP of contract, f.e. if we sell 1 contract per month and i choose 10% drop ratio, then the first drop will occur in tenth month or if we sell 2 contracts per month and drop ratio will be 10% then the first drop will ocur in fitfh month. Now i have problem how to show second, third, forth etc. drop flags on the same "timeline" alongside the first drop flag. This needs to be tied to every 10 contracts sold somehow, but I can't figure out how. Drop flag should appear dynamically based on contracts per month and choosen drop ratio. Maybe some one could help me. When i'll know in which month will occur a drop, then i will know in which month to make payment substraction.
i'm struggling with one task. The point is, that i have simple calculation of sales: we sell the same amount of contracts every month, which increases the overall portfolio. But now I need to insert a flag that will tell me in which month the contract drop will occur. We have two scenarios: 1. if drops 1 of 10 contracts (10% or in other words, every tenth contract) and 2. if drops 3 of 10 contracts (30%). I have created some calculations that shows me the FIRST DROP of contract, f.e. if we sell 1 contract per month and i choose 10% drop ratio, then the first drop will occur in tenth month or if we sell 2 contracts per month and drop ratio will be 10% then the first drop will ocur in fitfh month. Now i have problem how to show second, third, forth etc. drop flags on the same "timeline" alongside the first drop flag. This needs to be tied to every 10 contracts sold somehow, but I can't figure out how. Drop flag should appear dynamically based on contracts per month and choosen drop ratio. Maybe some one could help me. When i'll know in which month will occur a drop, then i will know in which month to make payment substraction.
| sales and drops.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | Drop ratio | 10% | Choose 10% or 30% drop ratio | ||||||||||||||||||||
| 2 | |||||||||||||||||||||||
| 3 | Contracts per month | 1 | Insert contracts per month | ||||||||||||||||||||
| 4 | Payment | 30 | Insert payment amount | ||||||||||||||||||||
| 5 | |||||||||||||||||||||||
| 6 | |||||||||||||||||||||||
| 7 | Substracted value | 30 | |||||||||||||||||||||
| 8 | Droped contracts | 1 | |||||||||||||||||||||
| 9 | First drop flag | drop | |||||||||||||||||||||
| 10 | Month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | ||
| 11 | Contracts per month | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
| 12 | Total contracts | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | ||
Sales | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B7:U7 | B7 | =IF(B9="drop",B8*$B$4,"") |
| B8:U8 | B8 | =IF(B9="drop",ROUNDDOWN(B12*$B$1,0),"") |
| B9 | B9 | =IF(B12>=10,"drop","") |
| C9:U9 | C9 | =IF(C12>=10,IF(B12>=10,"","drop"),"") |
| B11:U11 | B11 | =$B$3 |
| B12 | B12 | =B11 |
| C12:U12 | C12 | =B12+C11 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B9:U9 | Cell Value | contains "drop" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B1 | List | =Param!$A$2:$A$3 |
