Hi there,
in the green area, I'm trying to achieve below
1. slot the values in Col B in the corresponding year col
2. where there is capital expenses, I want it to show half in first year and other half the year after. e.g. row 10 has 800 in 2023, I want it to show 400 in 2023 and 400 in 2024
So is this possible? I have tried several If variations and nothing seem to work to meet all the considerations...
any help is much appreciated!
in the green area, I'm trying to achieve below
1. slot the values in Col B in the corresponding year col
2. where there is capital expenses, I want it to show half in first year and other half the year after. e.g. row 10 has 800 in 2023, I want it to show 400 in 2023 and 400 in 2024
So is this possible? I have tried several If variations and nothing seem to work to meet all the considerations...
any help is much appreciated!
Sample Data.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | 15,300.0 | - | - | 1,600 | 1,100 | 2,600 | 2,000 | 700 | 2,200 | 1,400 | 1,500 | ||||||||||||||||
2 | Year | $ (in '000$) | Comments | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | ||||
3 | 2024 | 100.0 | Artwork | - | - | - | 100 | - | - | - | - | - | - | 100 | 100 | 100 | 100 | 100 | 100 | 100 | |||||||
4 | 2024 | 200.0 | Others Expenses/materials w.off | - | - | - | 200 | - | - | - | - | - | - | 200 | 200 | 200 | 200 | 200 | 200 | 200 | |||||||
5 | 2024 | 300.0 | OCNIS | - | - | - | 300 | - | - | - | - | - | - | 300 | 300 | 300 | 300 | 300 | 300 | 300 | |||||||
6 | 2026 | 400.0 | SG&A | - | - | - | - | - | 400 | - | - | - | - | 400 | 400 | 400 | 400 | 400 | 400 | 400 | |||||||
7 | 2024 | 500.0 | Others Expenses/materials w.off | - | - | - | 500 | - | - | - | - | - | - | 500 | 500 | 500 | 500 | 500 | 500 | 500 | |||||||
8 | 2023 | 600.0 | Others Expenses/materials w.off | - | - | - | - | - | - | - | - | - | - | 600 | 600 | 600 | 600 | 600 | 600 | 600 | |||||||
9 | 2027 | 700.0 | Others Expenses/materials w.off | - | - | - | - | - | - | 700 | - | - | - | 700 | 700 | 700 | 700 | 700 | 700 | 700 | |||||||
10 | 2023 | 800.0 | Capital Expenses | - | - | 400 | - | - | - | - | - | - | - | 800 | |||||||||||||
11 | 2025 | 900.0 | Capital Expenses | - | - | - | - | 900 | - | - | - | - | - | 900 | |||||||||||||
12 | 2028 | 1,000.0 | Capital Expenses | - | - | - | - | - | - | - | 1,000 | - | - | 1,000 | |||||||||||||
13 | 2023 | 1,100.0 | Capital Expenses | - | - | 550 | - | - | - | - | - | - | - | 1,100 | |||||||||||||
14 | 2028 | 1,200.0 | Capital Expenses | - | - | - | - | - | - | - | 1,200 | - | - | 1,200 | |||||||||||||
15 | 2023 | 1,300.0 | Capital Expenses | - | - | 650 | - | - | - | - | - | - | - | 1,300 | |||||||||||||
16 | 2029 | 1,400.0 | Commercial costs | - | - | - | - | - | - | - | - | 1,400 | - | 1,400 | |||||||||||||
17 | 2030 | 1,500.0 | Commercial costs | - | - | - | - | - | - | - | - | - | 1,500 | 1,500 | |||||||||||||
18 | 2026 | 1,600.0 | Commercial costs | - | - | - | - | - | 1,600 | - | - | - | - | 1,600 | |||||||||||||
19 | 2025 | 1,700.0 | Commercial costs | - | - | - | - | 1,700 | - | - | - | - | - | 1,700 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1,E1:N1 | B1 | =SUBTOTAL(9,B3:B4962) |
E3:F19,H3:N19 | E3 | =IF(E$2=$A3,$B3,0) |
G3:G19 | G3 | =IF((AND(G$2=$A3,C3="Capital Expenses")),$B3/2,0) |
S3:S9 | S3 | =B3 |
T3:Y9 | T3 | =S3 |
R10:R15 | R10 | =B10*1 |
R16:R19 | R16 | =B16 |