Hi everyone,
on this fine Sunday evening, I'm trying to sort the logic out for below. Annoyingly, there are several options in cells B3 & C3 so trying to cater to that also. I'm trying to tell the formula that if C5= Growth then show me the correct sales numbers per below and the choices in B3 & C3 and if it says without growth, I want the 2021 value until 2030 for the same choices in B3 & C3. Sounds simple enough but I'm flabbergasted!
please help!
thank you!
on this fine Sunday evening, I'm trying to sort the logic out for below. Annoyingly, there are several options in cells B3 & C3 so trying to cater to that also. I'm trying to tell the formula that if C5= Growth then show me the correct sales numbers per below and the choices in B3 & C3 and if it says without growth, I want the 2021 value until 2030 for the same choices in B3 & C3. Sounds simple enough but I'm flabbergasted!
please help!
thank you!

Sample Data 18.07.21.xlsx | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | ||||||||||||||||||||||
2 | ||||||||||||||||||||||
3 | All | All Brands | All | All | Ply | Growth | ||||||||||||||||
4 | Both | Wood | All | Sandal | Without Growth | |||||||||||||||||
5 | Without Growth | Plastic | All | Ergo | ||||||||||||||||||
6 | All | Frivo | ||||||||||||||||||||
7 | All | All Brands | ||||||||||||||||||||
8 | Wood | Ply | ||||||||||||||||||||
9 | All | All Brands | Wood | Sandal | ||||||||||||||||||
10 | NPV 10 Years | Wood | Total Wood | |||||||||||||||||||
11 | USD '000 FX@FBP20 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | Plastic | Ergo | |||||||||
12 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Plastic | Frivo | ||||||||||
13 | Net Trade Sales | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | Plastic | Total Plastic | |||||||||
14 | ||||||||||||||||||||||
15 | ||||||||||||||||||||||
16 | ||||||||||||||||||||||
17 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | ||||||||||||
18 | Ply | 100 | 200 | 300 | 500 | 800 | 1,300 | 2,100 | 3,400 | 5,500 | 8,900 | Wood | ||||||||||
19 | Sandal | 200 | 250 | 450 | 700 | 1,150 | 1,850 | 3,000 | 4,850 | 7,850 | 12,700 | Wood | ||||||||||
20 | Ergo | 250 | 275 | 525 | 800 | 1,325 | 2,125 | 3,450 | 5,575 | 9,025 | 14,600 | Plastic | ||||||||||
21 | Frivo | 300 | 325 | 625 | 950 | 1,575 | 2,525 | 4,100 | 6,625 | 10,725 | 17,350 | Plastic | ||||||||||
22 | 850 | 1,050 | 1,900 | 2,950 | 4,850 | 7,800 | 12,650 | 20,450 | 33,100 | 53,550 | ||||||||||||
23 | ||||||||||||||||||||||
24 | ||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A9:B9 | A9 | =B3 |
D13:M13 | D13 | =IF(C5="Growth",IF($C$3="All Brands",C22/1000,IF(LEFT($C$3,5)="Total",SUMIF(M18:M21,B3,C18:C21)/1000,IF($C$3="All Brands",C22/1000,SUMIF(M18:M21,B3,C18:C21)/1000)))) |
E18:L21 | E18 | =D18+C18 |
C22:L22 | C22 | =SUM(C18:C21) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C5 | List | =$S$3:$S$4 |
B3 | List | =$P$3:$P$5 |
C3 | List | =OFFSET($R$3,MATCH($B$3,$Q$3:$Q$14,0),0,COUNTIF(Q2:Q14,$B$3),1) |