Hi there brains trust! Hope you are all well
I have a question involving dates and lookups/if formulas - clearly i am doing something wrong as i cant get the right answer
I am needing to calculate commissions monthly and include previous months paid and not paid in columns following but i cant seem to get the formulas to work.
Clearly I am doing something wrong and really need some advice have spent 2 weeks on this already!
I have colour coded for ease of following the spreadsheet (have removed names for obvious reasons) and formulas but if someone can see a glaringly obvious error please could you help.
Have an awesome day all
I have a question involving dates and lookups/if formulas - clearly i am doing something wrong as i cant get the right answer
I am needing to calculate commissions monthly and include previous months paid and not paid in columns following but i cant seem to get the formulas to work.
Clearly I am doing something wrong and really need some advice have spent 2 weeks on this already!
I have colour coded for ease of following the spreadsheet (have removed names for obvious reasons) and formulas but if someone can see a glaringly obvious error please could you help.
Have an awesome day all
excel commission help1.xlsx | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
2 | ||||||||||||||||||||||
3 | October 2020 | Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised | ||||||||||||||||||||
4 | ||||||||||||||||||||||
5 | Date | Invoice No. | Customer PO No. | Customer Name | Amount | Amount Due | Status | Month end | Date Closed (date paid) | Salesperson | salesperson | invoice no | commiss | paid | not paid | |||||||
6 | ||||||||||||||||||||||
7 | 3/10/2020 | 1148567 | 2146566/VO472895 | 2834 | 0 | Closed | 44136 | 11/12/2020 | 1148567 | n | 0 | 2834 | 0 | |||||||||
8 | 5/10/2020 | 1148611 | 2146539 | 2557.5 | 0 | Closed | 44136 | 11/12/2020 | 1148611 | y | #N/A | #N/A | 0 | |||||||||
9 | 27/10/2020 | 1148683 | 295 | -5 | Credit | 44136 | 1148683 | n | 0 | 295 | 0 | |||||||||||
10 | 26/10/2020 | 1148690 | 2137991 | 6882.7 | 0 | Closed | 44136 | 11/12/2020 | 1148690 | y | #N/A | #N/A | 0 | |||||||||
11 | 26/10/2020 | 1148728 | 2161436 | 7810 | 0 | Closed | 44136 | 11/12/2020 | 1148728 | n | 0 | 7810 | 0 | |||||||||
12 | ||||||||||||||||||||||
13 | IF($I12=$B$7,,IF($J12<$B8,"n","y")) | |||||||||||||||||||||
14 | ||||||||||||||||||||||
15 | IF$O12="n",,VLOOKUP($N12,$C$11:$H$152,4)) | |||||||||||||||||||||
16 | ||||||||||||||||||||||
17 | IF($O12="y",VLOOKUP($N12,$C$11:$H$152,4),IF($O12="n",F12)) | |||||||||||||||||||||
18 | ||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N7:N11 | N7 | =C7 |
O7:O11 | O7 | =IF($I7=$B$7,,IF($J7<$B3,"n","y")) |
P7:P11 | P7 | =IF($O7="n",,VLOOKUP($N7,$C$11:$H$152,4)) |
Q7:Q11 | Q7 | =IF($O7="y",VLOOKUP($N7,$C$11:$H$152,4),IF($O7="n",F7)) |
S7:S11 | S7 | =IF(J7>I7,,F7) |