Possible If and something else?!

A

anum

New Member
Joined
Aug 14, 2012
Messages
44
Office Version
  1. 365
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!

Sample Data.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
115,300.0--1,6001,1002,6002,0007002,2001,4001,500
2Year$ (in '000$)Comments20212022202320242025202620272028202920302021202220232024202520262027202820292030
32024100.0Artwork---100------100100100100100100100
42024200.0Others Expenses/materials w.off---200------200200200200200200200
52024300.0OCNIS---300------300300300300300300300
62026400.0SG&A-----400----400400400400400400400
72024500.0Others Expenses/materials w.off---500------500500500500500500500
82023600.0Others Expenses/materials w.off----------600600600600600600600
92027700.0Others Expenses/materials w.off------700---700700700700700700700
102023800.0Capital Expenses--400-------800
112025900.0Capital Expenses----900-----900
1220281,000.0Capital Expenses-------1,000--1,000
1320231,100.0Capital Expenses--550-------1,100
1420281,200.0Capital Expenses-------1,200--1,200
1520231,300.0Capital Expenses--650-------1,300
1620291,400.0Commercial costs--------1,400-1,400
1720301,500.0Commercial costs---------1,5001,500
1820261,600.0Commercial costs-----1,600----1,600
1920251,700.0Commercial costs----1,700-----1,700
Sheet1
Cell Formulas
RangeFormula
B1,E1:N1B1=SUBTOTAL(9,B3:B4962)
E3:F19,H3:N19E3=IF(E$2=$A3,$B3,0)
G3:G19G3=IF((AND(G$2=$A3,C3="Capital Expenses")),$B3/2,0)
S3:S9S3=B3
T3:Y9T3=S3
R10:R15R10=B10*1
R16:R19R16=B16
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
See if this is what you mean.
20210604 If split capital accross 2 years.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
115300002200270021502450700110025001500
2Year$ (in '000$)Comments20212022202320242025202620272028202920302021202220232024202520262027202820292030
32024100Artwork000100000000100100100100100100100
42024200Others Expenses/materials w.off000200000000200200200200200200200
52024300OCNIS000300000000300300300300300300300
62026400SG&A000004000000400400400400400400400
72024500Others Expenses/materials w.off000500000000500500500500500500500
82023600Others Expenses/materials w.off006000000000600600600600600600600
92027700Others Expenses/materials w.off000000700000700700700700700700700
102023800Capital Expenses00400400000000800
112025900Capital Expenses00004504500000900
1220281000Capital Expenses000000050050001000
1320231100Capital Expenses005505500000001100
1420281200Capital Expenses000000060060001200
1520231300Capital Expenses006506500000001300
1620291400Commercial costs00000000140001400
1720301500Commercial costs00000000015001500
1820261600Commercial costs00000160000001600
1920251700Commercial costs00001700000001700
Sheet1
Cell Formulas
RangeFormula
B1,E1:N1B1=SUBTOTAL(9,B3:B4962)
E3:N19E3=IF($C3="Capital Expenses", IF(OR($A3=E$2,($A3+1)=E$2),$B3/2,0), IF($A3=E$2,$B3,0))
S3:S9S3=B3
T3:Y9T3=S3
R10:R15R10=B10*1
R16:R19R16=B16
 
Upvote 0
Alex Blakenburg said:
See if this is what you mean.
20210604 If split capital accross 2 years.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
115300002200270021502450700110025001500
2Year$ (in '000$)Comments20212022202320242025202620272028202920302021202220232024202520262027202820292030
32024100Artwork000100000000100100100100100100100
42024200Others Expenses/materials w.off000200000000200200200200200200200
52024300OCNIS000300000000300300300300300300300
62026400SG&A000004000000400400400400400400400
72024500Others Expenses/materials w.off000500000000500500500500500500500
82023600Others Expenses/materials w.off006000000000600600600600600600600
92027700Others Expenses/materials w.off000000700000700700700700700700700
102023800Capital Expenses00400400000000800
112025900Capital Expenses00004504500000900
1220281000Capital Expenses000000050050001000
1320231100Capital Expenses005505500000001100
1420281200Capital Expenses000000060060001200
1520231300Capital Expenses006506500000001300
1620291400Commercial costs00000000140001400
1720301500Commercial costs00000000015001500
1820261600Commercial costs00000160000001600
1920251700Commercial costs00001700000001700
Sheet1
Cell Formulas
RangeFormula
B1,E1:N1B1=SUBTOTAL(9,B3:B4962)
E3:N19E3=IF($C3="Capital Expenses", IF(OR($A3=E$2,($A3+1)=E$2),$B3/2,0), IF($A3=E$2,$B3,0))
S3:S9S3=B3
T3:Y9T3=S3
R10:R15R10=B10*1
R16:R19R16=B16
Click to expand...
I'm still wrapping my head around how you pulled this off! amazing, thank you so much Alex!!

have a fab weekend all!
 
Upvote 0
Hi guys, sorry. I forgot a category to take into account also.

for CAPITAL EXPS ONLY where it says source - need first year to be 5% and second year to be 95%
make - first year 25% and 2nd year 75%
commercial - 1st yr 40% and 2nd yr 60%

is this possible? thanks again!


Sample Data.xlsx
ABCDEFGHIJKLMNO
2PillarYear$ (in '000$)Comments2021202220232024202520262027202820292030
3Source2024100.0Artwork---100------
4Source2024200.0Others Expenses/materials w.off---200------
5Source2024300.0OCNIS---300------
6Source2026400.0SG&A-----400----
7Source2024500.0Others Expenses/materials w.off---500------
8Source2023600.0Others Expenses/materials w.off--600-------
9Source2027700.0Others Expenses/materials w.off------700---
10Source2023800.0Capital Expenses--400400------
11Source2025900.0Capital Expenses----450450----
12Make20281,000.0Capital Expenses-------500500-
13Source20231,100.0Capital Expenses--550550------
14Commercial20281,200.0Capital Expenses-------600600-
15Source20231,300.0Capital Expenses--650650------
16Source20291,400.0Commercial costs--------1,400-
17Source20301,500.0Commercial costs---------1,500
18Source20261,600.0Commercial costs-----1,600----
19Source20251,700.0Commercial costs----1,700-----
Sheet1
Cell Formulas
RangeFormula
F3:O19F3=IF($D3="Capital Expenses",IF(OR($B3=F$2,($B3+1)=F$2),$C3/2,0),IF($B3=F$2,$C3,0))
 
Upvote 0
Peter_SSs said:
Could you (carefully) manually enter the values that you would expect in the green area from that sample data and post it again?
Click to expand...
same as above but for CAPITAL EXPS ONLY where it says source - need first year to be 5% and second year to be 95%
make - first year 25% and 2nd year 75%
commercial - 1st yr 40% and 2nd yr 60%
 
Upvote 0
Ok.
Copy this table somewhere and give it the table name - tblCapAlloc
20210604 If split capital accross 2 years v02.xlsx
ABCDE
1
2Capital Expenses Allocation
3
4PillarYear 1Year 2
5Source5%95%
6Make25%75%
7Commercial40%60%
8
Parameters


Then see if this works for you:-

20210604 If split capital accross 2 years v02.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1153000076041401745285570073028701500
2PillarYear$ (in '000$)Comments20212022202320242025202620272028202920302021202220232024202520262027202820292030
3Source2024100Artwork000100000000100100100100100100100
4Source2024200Others Expenses/materials w.off000200000000200200200200200200200
5Source2024300OCNIS000300000000300300300300300300300
6Source2026400SG&A000004000000400400400400400400400
7Source2024500Others Expenses/materials w.off000500000000500500500500500500500
8Source2023600Others Expenses/materials w.off006000000000600600600600600600600
9Source2027700Others Expenses/materials w.off000000700000700700700700700700700
10Source2023800Capital Expenses0040760000000800
11Source2025900Capital Expenses0000458550000900
12Make20281000Capital Expenses000000025075001000
13Source20231100Capital Expenses005510450000001100
14Commercial20281200Capital Expenses000000048072001200
15Source20231300Capital Expenses006512350000001300
16Source20291400Commercial costs00000000140001400
17Source20301500Commercial costs00000000015001500
18Source20261600Commercial costs00000160000001600
19Source20251700Commercial costs00001700000001700
Data
Cell Formulas
RangeFormula
C1,F1:O1C1=SUBTOTAL(9,C3:C4962)
F3:O19F3=IF($D3<>"Capital Expenses", IF(F$2=$B3,$C3,0), IF(OR($B3=F$2,($B3+1)=F$2), $C3*VLOOKUP($A3,tblCapAlloc,F$2-$B3+2,FALSE), 0))
T3:T9T3=C3
U3:Z9U3=T3
S10:S15S10=C10*1
S16:S19S16=C16
 
Upvote 0
Solution
Alex Blakenburg said:
Ok.
Copy this table somewhere and give it the table name - tblCapAlloc
20210604 If split capital accross 2 years v02.xlsx
ABCDE
1
2Capital Expenses Allocation
3
4PillarYear 1Year 2
5Source5%95%
6Make25%75%
7Commercial40%60%
8
Parameters


Then see if this works for you:-

20210604 If split capital accross 2 years v02.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1153000076041401745285570073028701500
2PillarYear$ (in '000$)Comments20212022202320242025202620272028202920302021202220232024202520262027202820292030
3Source2024100Artwork000100000000100100100100100100100
4Source2024200Others Expenses/materials w.off000200000000200200200200200200200
5Source2024300OCNIS000300000000300300300300300300300
6Source2026400SG&A000004000000400400400400400400400
7Source2024500Others Expenses/materials w.off000500000000500500500500500500500
8Source2023600Others Expenses/materials w.off006000000000600600600600600600600
9Source2027700Others Expenses/materials w.off000000700000700700700700700700700
10Source2023800Capital Expenses0040760000000800
11Source2025900Capital Expenses0000458550000900
12Make20281000Capital Expenses000000025075001000
13Source20231100Capital Expenses005510450000001100
14Commercial20281200Capital Expenses000000048072001200
15Source20231300Capital Expenses006512350000001300
16Source20291400Commercial costs00000000140001400
17Source20301500Commercial costs00000000015001500
18Source20261600Commercial costs00000160000001600
19Source20251700Commercial costs00001700000001700
Data
Cell Formulas
RangeFormula
C1,F1:O1C1=SUBTOTAL(9,C3:C4962)
F3:O19F3=IF($D3<>"Capital Expenses", IF(F$2=$B3,$C3,0), IF(OR($B3=F$2,($B3+1)=F$2), $C3*VLOOKUP($A3,tblCapAlloc,F$2-$B3+2,FALSE), 0))
T3:T9T3=C3
U3:Z9U3=T3
S10:S15S10=C10*1
S16:S19S16=C16
Click to expand...
That really did the trick. Thanks so much Alex!! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Adding new code to existing macro to account for locked/unlocked cells
Replies
1
Views
425
DanteAmor
DanteAmor
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
C
  • Question Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
511
chittunoo
C
D
  • Question Question
Difficult Excel Formula - Populating Data from Drop Down Menu
Replies
0
Views
606
DataValidator
D
M
  • Question Question
Looking Urgent Help : SUMIFS functions with HLOOKUP/INDEX & MATCH functions
Replies
2
Views
1K
Weazel
Weazel

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top