snuffnchess
Board Regular
- Joined
- May 15, 2015
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
I have put together a very small sampling of test data to show what I am trying to accomplish, but am not 100% of how to go about putting this in action.
We have a list of owners that pay a certain percent of margin of sales as a royalty payment. Which if it were just left at that it would be simple. There are then some exceptions where the percentages change based on the customer they are for etc. Then there are certain owners that we look back at a 3 week history for, and adjust numbers if the numbers have changed.
From the Fixed Data tab of the workbook, it will have the percentages, the exception accounts, minimum fees (if for some reason the minimum is not hit), and then column F to reflect if we are looking at the data from 3 weeks ago.
Current Tab has the margin in $ on it from current export and the margin for the exception accounts
3 wk original tab has the margin in $ as it appeared 3 weeks ago on reports
3 wk updated tab has the margin in $ as it appears today when looking at info from 3 weeks ago (there are sometimes adjustments that come in over time)
Desired result is ultimately how we want the data to look for the end result. (without the colors - i added those in just so that it was more obvious where the calculations came from).
Where do I even begin with this?
Fixed Data
Current
3 wk Original
3 wk Updated
Desired Result
We have a list of owners that pay a certain percent of margin of sales as a royalty payment. Which if it were just left at that it would be simple. There are then some exceptions where the percentages change based on the customer they are for etc. Then there are certain owners that we look back at a 3 week history for, and adjust numbers if the numbers have changed.
From the Fixed Data tab of the workbook, it will have the percentages, the exception accounts, minimum fees (if for some reason the minimum is not hit), and then column F to reflect if we are looking at the data from 3 weeks ago.
Current Tab has the margin in $ on it from current export and the margin for the exception accounts
3 wk original tab has the margin in $ as it appeared 3 weeks ago on reports
3 wk updated tab has the margin in $ as it appears today when looking at info from 3 weeks ago (there are sometimes adjustments that come in over time)
Desired result is ultimately how we want the data to look for the end result. (without the colors - i added those in just so that it was more obvious where the calculations came from).
Where do I even begin with this?
Fixed Data
Start.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | Owner | Item 1 | Item 3 | Item 2 | Item 4 | 3wk Comparison | Minimum | Min Waive | Alt Fee | Item 3 Exception % 1 | Item 3 Exception % 2 | Item 1 Exception Name 1 | Item 1 Exception % 1 | Item 1 Exception Name 2 | Item 1 Exception % 2 | Item 1 Exception Name 3 | Item 1 Exception % 3 | Notes | ||
2 | Group 1 | 18% | 24% | 15% | 5% | n | $ 250.00 | |||||||||||||
3 | Group 2 | 18% | 24% | 15% | 5% | y | $ 250.00 | |||||||||||||
4 | Group 3 | 17% | 20% | 17% | 5% | y | $ 125.00 | 10% | 10% | |||||||||||
Fixed Data |
Current
Start.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | Owner | Item 1 Margin | Item 2 Margin | Item 3 Margin | Item 4 Margin | Item 3 Exception Name 1 | Item 3 Exception Margin 1 | Item 3 Exception Name 2 | Item 3 Exception Margin 2 | Item 1 Exception Name 1 | Item 1 Exception Margin 1 | Item 1 Exception Name 2 | Item 1 Exception Margin 2 | Item 1 Exception Name 3 | Item 1 Exception Margin 3 | Notes | ||
2 | Group 1 | $ 144.03 | $ 175.40 | $ - | $ - | |||||||||||||
3 | Group 2 | $ 684.55 | $ 41.02 | $ - | $ - | |||||||||||||
4 | Group 3 | $ 12,273.59 | $ 4,186.31 | $ 3,028.64 | $ - | Company 11396624 | $ 1,468.17 | Company 11255381 | $ 190.38 | |||||||||
Current |
3 wk Original
Start.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Owner | Item 1 Margin | Item 2 Margin | Item 3 Margin | Item 4 Margin | Item 3 Exception Name 1 | Item 3 Exception Margin 1 | Item 3 Exception Name 2 | Item 3 Exception Margin 2 | Item 1 Exception Name 1 | Item 1 Exception Margin 1 | Item 1 Exception Name 2 | Item 1 Exception Margin 2 | Item 1 Exception Name 3 | Item 1 Exception Margin 3 | ||
2 | Group 1 | $ 156.92 | $ 375.42 | $ 5.67 | $ - | ||||||||||||
3 | Group 2 | $ 34.01 | $ 91.93 | $ - | $ - | ||||||||||||
4 | Group 3 | $ 11,783.00 | $ 4,019.90 | $ 2,907.50 | $ - | Company 11396624 | $ 1,409.40 | Company 11255381 | $ 182.76 | ||||||||
3 Wk Original |
3 wk Updated
Start.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Owner | Item 1 Margin | Item 2 Margin | Item 3 Margin | Item 4 Margin | Item 3 Exception Name 1 | Item 3 Exception Margin 1 | Item 3 Exception Name 2 | Item 3 Exception Margin 2 | Item 1 Exception Name 1 | Item 1 Exception Margin 1 | Item 1 Exception Name 2 | Item 1 Exception Margin 2 | Item 1 Exception Name 3 | Item 1 Exception Margin 3 | ||
2 | Group 1 | $ 156.92 | $ 375.42 | $ 5.67 | $ - | ||||||||||||
3 | Group 2 | $ 32.96 | $ 91.93 | $ - | $ - | ||||||||||||
4 | Group 3 | $ 11,783.00 | $ 4,118.90 | $ 2,907.42 | $ - | Company 11396624 | $ 1,409.40 | Company 11255381 | $ 182.76 | ||||||||
3 Wk Updated |
Desired Result
Start.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | |||||||||||||
2 | Owner | Description | Rate | Margin | Current Fee | Minimum | Adjustment | Alternate Fees | Invoice Total | ||||
3 | Group 1 | $ 319.43 | $ 250.00 | $ 319.43 | |||||||||
4 | Item 3 | 24% | $ - | ||||||||||
5 | Item 1 | 18% | $ 144.03 | ||||||||||
6 | Item 2 | 15% | $ 175.40 | ||||||||||
7 | Item 4 | 5% | $ - | ||||||||||
8 | |||||||||||||
9 | Group 2 | ||||||||||||
10 | Item 3 | 15% | $ - | $ 725.57 | $ 250.00 | $ (1.05) | $ 724.52 | ||||||
11 | Item 1 | 18% | $ 684.55 | ||||||||||
12 | Item 2 | 15% | $ 41.02 | ||||||||||
13 | Item 4 | 5% | $ - | ||||||||||
14 | Item 3 - Original | 15% | $ - | ||||||||||
15 | Item 1 - Original | 18% | $ 34.01 | ||||||||||
16 | Item 2 - Original | 15% | $ 91.93 | ||||||||||
17 | Item 4 - Original | 5% | $ - | ||||||||||
18 | Item 3 - Updated | 15% | $ - | ||||||||||
19 | Item 1 - Updated | 18% | $ 32.96 | ||||||||||
20 | Item 2 - Updated | 15% | $ 91.93 | ||||||||||
21 | Item 4 - Updated | 5% | $ - | ||||||||||
22 | |||||||||||||
23 | Group 3 | ||||||||||||
24 | Item 3 | 20% | $ 3,028.64 | $ 21,147.09 | $ 98.92 | $ 125.00 | $ 21,371.01 | ||||||
25 | Item 3 - Exc. 1 | 10% | $ 1,468.17 | ||||||||||
26 | Item 3 - Exc. 2 | 10% | $ 190.38 | ||||||||||
27 | Item 1 | 17% | $ 12,273.59 | ||||||||||
28 | Item 2 | 17% | $ 4,186.31 | ||||||||||
29 | Item 4 | 5% | $ - | ||||||||||
30 | Item 3 - Original | 20% | $ 2,907.50 | ||||||||||
31 | Item 3 - Exc. 1 - Original | 10% | $ 1,409.40 | ||||||||||
32 | Item 3 - Exc. 2 - Original | 10% | $ 182.76 | ||||||||||
33 | Item 1 - Original | 17% | $ 11,783.00 | ||||||||||
34 | Item 2 - Original | 17% | $ 4,019.90 | ||||||||||
35 | Item 4 - Original | 5% | $ - | ||||||||||
36 | Item 3 - Updated | 20% | $ 2,907.42 | ||||||||||
37 | Item 3 - Exc. 1 - Updated | 10% | $ 1,409.40 | ||||||||||
38 | Item 3 - Exc. 2 - Updated | 10% | $ 182.76 | ||||||||||
39 | Item 1 - Updated | 17% | $ 11,783.00 | ||||||||||
40 | Item 2 - Updated | 17% | $ 4,118.90 | ||||||||||
41 | Item 4 - Updated | 5% | $ - | ||||||||||
42 | |||||||||||||
Desired Result |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3 | F3 | =SUM(E4:E7) |
F10 | F10 | =SUM(E10:E13) |
H10 | H10 | =SUM(E18:E21)-SUM(E14:E17) |
J10 | J10 | =H10+F10 |
F24 | F24 | =SUM(E24:E29) |
H24 | H24 | =SUM(E36:E41)-SUM(E30:E35) |
J24 | J24 | =F24+H24+I24 |