willgarling
New Member
- Joined
- Jul 5, 2011
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
Okay forum, I have been racking my brain and cannot figure this one out. I need to find the tier depletion allowance from table 2 for the tier selling price (table 2) that is closest without going over the actual selling price in table 1. Table 1 shows the product ID, the date sold, the selling price, and the depletion allowance for that price. I filled in the depletion allowance field with the value I need returned from table 2. Table 2 has the product ID, start and end date, and then columns showing tier selling prices and their corresponding tier depletion allowances. The date sold in table 1 must be in within the start and end dates in the corresponding product in table 2.
For product 157760, the selling price was $50.00. The closest tier selling price without going over from Table 2 is tier 3 selling price $52.35. so the depletion allowance is tier 3 16.70.
For product 881126, the date cold is 4/27/20. That date is outside the start and end dates for 881126 in table 2, so the depletion allowance is $0.00
For product 881127, the selling price is $50.35. The closest tier selling price without going over from table 2 is tier 1 $51.00, so the depletion allowance is tier 1 $7.65
Table 1
Table 2
For product 157760, the selling price was $50.00. The closest tier selling price without going over from Table 2 is tier 3 selling price $52.35. so the depletion allowance is tier 3 16.70.
For product 881126, the date cold is 4/27/20. That date is outside the start and end dates for 881126 in table 2, so the depletion allowance is $0.00
For product 881127, the selling price is $50.35. The closest tier selling price without going over from table 2 is tier 1 $51.00, so the depletion allowance is tier 1 $7.65
Table 1
Product ID | Date Sold | NET Price | Depletion Allowance |
157760 | 4/10/2020 | $ 50.00 | $ 16.70 |
881126 | 4/27/2020 | $ 52.35 | $ 0.00 |
881127 | 4/17/2020 | $ 50.35 | $ 7.65 |
Table 2
Product ID | Start Date | End Date | Tier 1 Selling Price | Tier 1 Depletion Allowance | Tier 2 Selling Price | Tier 2 Depletion Allowance | Tier 3 Selling Price | Tier 3 Depletion Allowance |
157760 | 4/1/2020 | 12/31/2020 | $ 63.75 | $ 9.50 | $ 62.85 | $ 9.50 | $ 52.35 | $ 16.70 |
881126 | 4/1/2020 | 4/10/2020 | $ 51.00 | $ 7.65 | $ 50.28 | $ 7.65 | $ 41.88 | $ 10.09 |
881127 | 4/1/2020 | 12/31/2020 | $ 51.00 | $ 7.65 | $ 50.28 | $ 7.65 | $ 41.88 | $ 10.09 |
881137 | 4/1/2020 | 12/31/2020 | $ 63.75 | $ 9.50 | $ 62.85 | $ 9.50 | $ 52.35 | $ 16.70 |
881139 | 4/1/2020 | 12/31/2020 | $ 51.00 | $ 7.65 | $ 50.28 | $ 7.65 | $ 41.88 | $ 10.09 |
881140 | 4/1/2020 | 12/31/2020 | $ 63.75 | $ 9.50 | $ 62.85 | $ 9.50 | $ 52.35 | $ 16.70 |