Advanced Excel Formula Help

shilo322

New Member
Joined
Apr 13, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that has a column of information that I need to look up in another sheet and return a formula calculation from that sheet. Basically, the Actual Work Load (AWL) is computed different based on the RU. The AWL Calculation is a formula that pertains to the SLAB sheet in the example. My limited excel knowledge says this should be easy...like an If RU from SLAB equals RU in AWL CALC then dump the formula that is in AWL CALC column B into the AWL column in the SLAB sheet and calculate it.

slab.png
calc.png
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Try this:

Book3.xlsx
ABCDE
1RUMonUnitsAmtAWL
2AT12760760760
3ZR12200024000730000
4BR660536303630
5BU1252624624
6TJ2521048.666667
Sheet1086
Cell Formulas
RangeFormula
E2:E6E2=CHOOSE(MATCH(A2,CALC!A$2:A$6,0),D2,C2*365,C2*B2,C2*B2,C2/12*B2)


Book3.xlsx
AB
1RUCalculation
2AT
3ZR
4BR
5BU
6TJ
CALC
 

shilo322

New Member
Joined
Apr 13, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you so much! That is so close! I need for the SLAB file to utilize the formula that is in the CALC sheet though. The solution provided doesn't account for the fact that I have 151 rows of different RU's with different calculation methods to be applied.

RUAWL CALC
AC=Units/12*MONTHS
AD=Units/12*MONTHS
AG=Units/12*MONTHS
AL=Units/12*MONTHS
AP=UNITS*MONTHS
AT=Amount
AZ=Units/12*MONTHS
BH=Units/12*MONTHS
C1=UNITS*MONTHS
C2=UNITS*MONTHS
C3=UNITS*MONTHS
C4=UNITS*MONTHS
C5=UNITS*MONTHS
C6=UNITS*MONTHS
CA=UNITS*MONTHS
CB=Units/12*MONTHS
CC=UNITS*MONTHS
CD=UNITS*MONTHS
CE=UNITS*MONTHS
CP=UNITS*MONTHS
CR=Units/12*MONTHS
CV=Units/12*MONTHS
CX=UNITS*MONTHS
DA=UNITS*MONTHS
DF=Units/12*MONTHS
DG=Units/12*MONTHS
DN=Units/12*MONTHS
DX=Units/12*MONTHS
EC=Units
EJ=UNITS*MONTHS
EM=UNITS*MONTHS
EN=UNITS*MONTHS
EP=UNITS*MONTHS
EQ=UNITS*MONTHS
EV=Units/12*MONTHS
FA=UNITS*MONTHS
FB=UNITS*MONTHS
FC=UNITS*MONTHS
FD=UNITS*MONTHS
FE=UNITS*MONTHS
FR=UNITS*MONTHS
FS=UNITS*MONTHS
FT=UNITS*MONTHS
G1=UNITS*MONTHS
G2=UNITS*MONTHS
G3=UNITS*MONTHS
H1=UNITS*MONTHS
H2=UNITS*MONTHS
H3=UNITS*MONTHS
H4=UNITS*MONTHS
H5=UNITS*MONTHS
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much! That is so close! I need for the SLAB file to utilize the formula that is in the CALC sheet though. The solution provided doesn't account for the fact that I have 151 rows of different RU's with different calculation methods to be applied.

That's not possible, what you have in CALC are Not formulas, they are Text describing formulas.
And Yes, my solution doesn't account for your 151 rows of different RUs, because that's not what your OP shows.

In order for me to update my formula to account for All the RU calculations, I'll need the complete list, But it'll be a Very Long formula.
 
Last edited:

shilo322

New Member
Joined
Apr 13, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
That's not possible, what you have in SLAB are Not formulas, they are Text describing formulas.
And Yes, my solution doesn't account for your 151 rows of different RUs, because that's not what your OP shows.

In order for me to update my formula to account for All the RU calculations, I'll need the complete list, But it'll be a Very Long formula.
I understand what I have in the CALC sheet is a listing of text to describe the formula that needs to be applied based on the RP. I thought there would be an easier way of doing it. My apologies for not being more clear in my OP. First time posting here. Thank you for your time!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
So if you post your complete CALC list, I can figure something out.
 

shilo322

New Member
Joined
Apr 13, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Book1.xlsx
AB
1RUAWL CALC
2AC=Units/12*MONTHS
3AD=Units/12*MONTHS
4AG=Units/12*MONTHS
5AL=Units/12*MONTHS
6AP=UNITS*MONTHS
7AT=Amount
8AZ=Units/12*MONTHS
9BH=Units/12*MONTHS
10C1=UNITS*MONTHS
11C2=UNITS*MONTHS
12C3=UNITS*MONTHS
13C4=UNITS*MONTHS
14C5=UNITS*MONTHS
15C6=UNITS*MONTHS
16CA=UNITS*MONTHS
17CB=Units/12*MONTHS
18CC=UNITS*MONTHS
19CD=UNITS*MONTHS
20CE=UNITS*MONTHS
21CP=UNITS*MONTHS
22CR=Units/12*MONTHS
23CV=Units/12*MONTHS
24CX=UNITS*MONTHS
25DA=UNITS*MONTHS
26DF=Units/12*MONTHS
27DG=Units/12*MONTHS
28DN=Units/12*MONTHS
29DX=Units/12*MONTHS
30EC=Units
31EJ=UNITS*MONTHS
32EM=UNITS*MONTHS
33EN=UNITS*MONTHS
34EP=UNITS*MONTHS
35EQ=UNITS*MONTHS
36EV=Units/12*MONTHS
37FA=UNITS*MONTHS
38FB=UNITS*MONTHS
39FC=UNITS*MONTHS
40FD=UNITS*MONTHS
41FE=UNITS*MONTHS
42FR=UNITS*MONTHS
43FS=UNITS*MONTHS
44FT=UNITS*MONTHS
45G1=UNITS*MONTHS
46G2=UNITS*MONTHS
47G3=UNITS*MONTHS
48H1=UNITS*MONTHS
49H2=UNITS*MONTHS
50H3=UNITS*MONTHS
51H4=UNITS*MONTHS
52H5=UNITS*MONTHS
53H6=UNITS*MONTHS
54H7=UNITS*MONTHS
55HA=UNITS*MONTHS
56HC=UNITS*MONTHS
57HD=UNITS*MONTHS
58HE=UNITS*MONTHS
59IV=UNITS*MONTHS
60J2=UNITS*MONTHS
61J3=UNITS*MONTHS
62J4=UNITS*MONTHS
63JB=UNITS*MONTHS
64JC=UNITS*MONTHS
65JD=UNITS*MONTHS
66KN=Units/12*MONTHS
67LA=UNITS*MONTHS
68LC=UNITS*MONTHS
69LD=UNITS*MONTHS
70LE=UNITS*MONTHS
71LF=UNITS*MONTHS
72LL=UNITS*MONTHS
73LM=UNITS*MONTHS
74LR=UNITS*MONTHS
75LU=UNITS*MONTHS
76LV=UNITS*MONTHS
77LW=UNITS*MONTHS
78LX=UNITS*MONTHS
79LY=UNITS*MONTHS
80LZ=UNITS*MONTHS
81ME=Units/12*MONTHS
82MJ=Units/12*MONTHS
83MS=UNITS*MONTHS
84MX=UNITS*MONTHS
85N1=UNITS*MONTHS*30
86N2=UNITS*MONTHS*30
87N5=UNITS*MONTHS*30
88N6=UNITS*MONTHS*30
89NA=Units/12*MONTHS
90NB=Units/12*MONTHS
91NL=Units/12*MONTHS
92NM=Units/12*MONTHS
93NS=Units/12*MONTHS
94NX=Units/12*MONTHS
95PR=Units/12*MONTHS
96PT=Units/12*MONTHS
97QA=UNITS*MONTHS
98QB=UNITS*MONTHS
99QC=UNITS*MONTHS
100QS=UNITS*MONTHS
101QU=UNITS*MONTHS
102QW=UNITS*MONTHS
103QX=UNITS*MONTHS
104RA=Amount
105SA=UNITS*MONTHS
106SD=UNITS*MONTHS
107SE=UNITS*MONTHS
108SK=UNITS*MONTHS
109SL=Units/12*MONTHS
110SM=Units/12*MONTHS
111SP=Units/12*MONTHS
112SR=UNITS*MONTHS
113SU=Units/12*MONTHS
114SX=Units/12*MONTHS
115SY=UNITS*MONTHS
116TS=Units/12*MONTHS
117UB=Units/12*MONTHS
118UE=Units/12*MONTHS
119UF=Units/12*MONTHS
120UG=Units/12*MONTHS
121V1=Units/12*MONTHS
122V2=UNITS*MONTHS
123V3=Units/12*MONTHS
124V4=UNITS*MONTHS
125V5=UNITS*MONTHS
126VA=UNITS*MONTHS
127VB=UNITS*MONTHS
128VC=UNITS*MONTHS
129VD=UNITS*MONTHS
130VE=UNITS*MONTHS
131VF=UNITS*MONTHS
132VG=UNITS*MONTHS
133VH=UNITS*MONTHS
134VJ=UNITS*MONTHS
135VK=UNITS*MONTHS
136VL=Units/12*MONTHS
137VM=Units/12*MONTHS
138VS=Units/12*MONTHS
139W2=UNITS*MONTHS*30
140W3=UNITS*8760
141W4=UNITS*8760
142W5=UNITS*365
143W6=UNITS*365
144W7=UNITS*365
145W8=UNITS*365
146WB=UNITS*MONTHS
147WL=Units/12*MONTHS
148WM=Units/12*MONTHS
149WS=Units/12*MONTHS
150WW=Units/12*MONTHS
151YY=Units/12*MONTHS
AWL CALC


I hope this works! :)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Thanks for posting the Table.

I've tested this to work, do some further testing yourself.

I Need you to Sort your CALC table by Column B (AWL CALC), alphabetically A to Z, Don't forget to "Expand selection", after the sort, it should look like my sample below.

Book3.xlsx
ABCDE
1RUMonUnitsAmtAWL
2AT12760760760
3EC122000240002000
4W566053630220825
5W31252624455520
6AP252104104
7N166053630108900
8AC125262452
Sheet1086
Cell Formulas
RangeFormula
E2:E8E2=CHOOSE(LOOKUP(MATCH(A2,CALC!A$2:A$151,0),{1,3,4,8,10,101,106},{1,2,3,4,5,6,7}),D2,C2,C2*365,C2*8760,C2*B2,C2*B2*30,C2/12*B2)


Book3.xlsx
AB
1RUAWL CALC
2AT=Amount
3RA=Amount
4EC=Units
5W5=Units*365
6W6=Units*365
7W7=Units*365
8W8=Units*365
9W3=Units*8760
10W4=Units*8760
11AP=UNITS*MONTHS
12C1=UNITS*MONTHS
13C2=UNITS*MONTHS
14C3=UNITS*MONTHS
15C4=UNITS*MONTHS
16C5=UNITS*MONTHS
17C6=UNITS*MONTHS
18CA=UNITS*MONTHS
19CC=UNITS*MONTHS
20CD=UNITS*MONTHS
21CE=UNITS*MONTHS
22CP=UNITS*MONTHS
23CX=UNITS*MONTHS
24DA=UNITS*MONTHS
25EJ=UNITS*MONTHS
26EM=UNITS*MONTHS
27EN=UNITS*MONTHS
28EP=UNITS*MONTHS
29EQ=UNITS*MONTHS
30FA=UNITS*MONTHS
31FB=UNITS*MONTHS
32FC=UNITS*MONTHS
33FD=UNITS*MONTHS
34FE=UNITS*MONTHS
35FR=UNITS*MONTHS
36FS=UNITS*MONTHS
37FT=UNITS*MONTHS
38G1=UNITS*MONTHS
39G2=UNITS*MONTHS
40G3=UNITS*MONTHS
41H1=UNITS*MONTHS
42H2=UNITS*MONTHS
43H3=UNITS*MONTHS
44H4=UNITS*MONTHS
45H5=UNITS*MONTHS
46H6=UNITS*MONTHS
47H7=UNITS*MONTHS
48HA=UNITS*MONTHS
49HC=UNITS*MONTHS
50HD=UNITS*MONTHS
51HE=UNITS*MONTHS
52IV=UNITS*MONTHS
53J2=UNITS*MONTHS
54J3=UNITS*MONTHS
55J4=UNITS*MONTHS
56JB=UNITS*MONTHS
57JC=UNITS*MONTHS
58JD=UNITS*MONTHS
59LA=UNITS*MONTHS
60LC=UNITS*MONTHS
61LD=UNITS*MONTHS
62LE=UNITS*MONTHS
63LF=UNITS*MONTHS
64LL=UNITS*MONTHS
65LM=UNITS*MONTHS
66LR=UNITS*MONTHS
67LU=UNITS*MONTHS
68LV=UNITS*MONTHS
69LW=Units*MONTHS
70LX=Units*MONTHS
71LY=Units*MONTHS
72LZ=Units*MONTHS
73MS=Units*MONTHS
74MX=Units*MONTHS
75QA=Units*MONTHS
76QB=Units*MONTHS
77QC=Units*MONTHS
78QS=Units*MONTHS
79QU=Units*MONTHS
80QW=Units*MONTHS
81QX=Units*MONTHS
82SA=Units*MONTHS
83SD=Units*MONTHS
84SE=Units*MONTHS
85SK=Units*MONTHS
86SR=Units*MONTHS
87SY=Units*MONTHS
88V2=Units*MONTHS
89V4=Units*MONTHS
90V5=Units*MONTHS
91VA=Units*MONTHS
92VB=Units*MONTHS
93VC=Units*MONTHS
94VD=Units*MONTHS
95VE=Units*MONTHS
96VF=Units*MONTHS
97VG=Units*MONTHS
98VH=Units*MONTHS
99VJ=Units*MONTHS
100VK=Units*MONTHS
101WB=Units*MONTHS
102N1=Units*MONTHS*30
103N2=Units*MONTHS*30
104N5=Units*MONTHS*30
105N6=Units*MONTHS*30
106W2=Units*MONTHS*30
107AC=Units/12*MONTHS
108AD=Units/12*MONTHS
109AG=Units/12*MONTHS
110AL=Units/12*MONTHS
111AZ=Units/12*MONTHS
112BH=Units/12*MONTHS
113CB=Units/12*MONTHS
114CR=Units/12*MONTHS
115CV=Units/12*MONTHS
116DF=Units/12*MONTHS
117DG=Units/12*MONTHS
118DN=Units/12*MONTHS
119DX=Units/12*MONTHS
120EV=Units/12*MONTHS
121KN=Units/12*MONTHS
122ME=Units/12*MONTHS
123MJ=Units/12*MONTHS
124NA=Units/12*MONTHS
125NB=Units/12*MONTHS
126NL=Units/12*MONTHS
127NM=Units/12*MONTHS
128NS=Units/12*MONTHS
129NX=Units/12*MONTHS
130PR=Units/12*MONTHS
131PT=Units/12*MONTHS
132SL=Units/12*MONTHS
133SM=Units/12*MONTHS
134SP=Units/12*MONTHS
135SU=Units/12*MONTHS
136SX=Units/12*MONTHS
137TS=Units/12*MONTHS
138UB=Units/12*MONTHS
139UE=Units/12*MONTHS
140UF=Units/12*MONTHS
141UG=Units/12*MONTHS
142V1=Units/12*MONTHS
143V3=Units/12*MONTHS
144VL=Units/12*MONTHS
145VM=Units/12*MONTHS
146VS=Units/12*MONTHS
147WL=Units/12*MONTHS
148WM=Units/12*MONTHS
149WS=Units/12*MONTHS
150WW=Units/12*MONTHS
151YY=Units/12*MONTHS
CALC
 
Solution

shilo322

New Member
Joined
Apr 13, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thanks for posting the Table.

I've tested this to work, do some further testing yourself.

I Need you to Sort your CALC table by Column B (AWL CALC), alphabetically A to Z, Don't forget to "Expand selection", after the sort, it should look like my sample below.

Book3.xlsx
ABCDE
1RUMonUnitsAmtAWL
2AT12760760760
3EC122000240002000
4W566053630220825
5W31252624455520
6AP252104104
7N166053630108900
8AC125262452
Sheet1086
Cell Formulas
RangeFormula
E2:E8E2=CHOOSE(LOOKUP(MATCH(A2,CALC!A$2:A$151,0),{1,3,4,8,10,101,106},{1,2,3,4,5,6,7}),D2,C2,C2*365,C2*8760,C2*B2,C2*B2*30,C2/12*B2)


Book3.xlsx
AB
1RUAWL CALC
2AT=Amount
3RA=Amount
4EC=Units
5W5=Units*365
6W6=Units*365
7W7=Units*365
8W8=Units*365
9W3=Units*8760
10W4=Units*8760
11AP=UNITS*MONTHS
12C1=UNITS*MONTHS
13C2=UNITS*MONTHS
14C3=UNITS*MONTHS
15C4=UNITS*MONTHS
16C5=UNITS*MONTHS
17C6=UNITS*MONTHS
18CA=UNITS*MONTHS
19CC=UNITS*MONTHS
20CD=UNITS*MONTHS
21CE=UNITS*MONTHS
22CP=UNITS*MONTHS
23CX=UNITS*MONTHS
24DA=UNITS*MONTHS
25EJ=UNITS*MONTHS
26EM=UNITS*MONTHS
27EN=UNITS*MONTHS
28EP=UNITS*MONTHS
29EQ=UNITS*MONTHS
30FA=UNITS*MONTHS
31FB=UNITS*MONTHS
32FC=UNITS*MONTHS
33FD=UNITS*MONTHS
34FE=UNITS*MONTHS
35FR=UNITS*MONTHS
36FS=UNITS*MONTHS
37FT=UNITS*MONTHS
38G1=UNITS*MONTHS
39G2=UNITS*MONTHS
40G3=UNITS*MONTHS
41H1=UNITS*MONTHS
42H2=UNITS*MONTHS
43H3=UNITS*MONTHS
44H4=UNITS*MONTHS
45H5=UNITS*MONTHS
46H6=UNITS*MONTHS
47H7=UNITS*MONTHS
48HA=UNITS*MONTHS
49HC=UNITS*MONTHS
50HD=UNITS*MONTHS
51HE=UNITS*MONTHS
52IV=UNITS*MONTHS
53J2=UNITS*MONTHS
54J3=UNITS*MONTHS
55J4=UNITS*MONTHS
56JB=UNITS*MONTHS
57JC=UNITS*MONTHS
58JD=UNITS*MONTHS
59LA=UNITS*MONTHS
60LC=UNITS*MONTHS
61LD=UNITS*MONTHS
62LE=UNITS*MONTHS
63LF=UNITS*MONTHS
64LL=UNITS*MONTHS
65LM=UNITS*MONTHS
66LR=UNITS*MONTHS
67LU=UNITS*MONTHS
68LV=UNITS*MONTHS
69LW=Units*MONTHS
70LX=Units*MONTHS
71LY=Units*MONTHS
72LZ=Units*MONTHS
73MS=Units*MONTHS
74MX=Units*MONTHS
75QA=Units*MONTHS
76QB=Units*MONTHS
77QC=Units*MONTHS
78QS=Units*MONTHS
79QU=Units*MONTHS
80QW=Units*MONTHS
81QX=Units*MONTHS
82SA=Units*MONTHS
83SD=Units*MONTHS
84SE=Units*MONTHS
85SK=Units*MONTHS
86SR=Units*MONTHS
87SY=Units*MONTHS
88V2=Units*MONTHS
89V4=Units*MONTHS
90V5=Units*MONTHS
91VA=Units*MONTHS
92VB=Units*MONTHS
93VC=Units*MONTHS
94VD=Units*MONTHS
95VE=Units*MONTHS
96VF=Units*MONTHS
97VG=Units*MONTHS
98VH=Units*MONTHS
99VJ=Units*MONTHS
100VK=Units*MONTHS
101WB=Units*MONTHS
102N1=Units*MONTHS*30
103N2=Units*MONTHS*30
104N5=Units*MONTHS*30
105N6=Units*MONTHS*30
106W2=Units*MONTHS*30
107AC=Units/12*MONTHS
108AD=Units/12*MONTHS
109AG=Units/12*MONTHS
110AL=Units/12*MONTHS
111AZ=Units/12*MONTHS
112BH=Units/12*MONTHS
113CB=Units/12*MONTHS
114CR=Units/12*MONTHS
115CV=Units/12*MONTHS
116DF=Units/12*MONTHS
117DG=Units/12*MONTHS
118DN=Units/12*MONTHS
119DX=Units/12*MONTHS
120EV=Units/12*MONTHS
121KN=Units/12*MONTHS
122ME=Units/12*MONTHS
123MJ=Units/12*MONTHS
124NA=Units/12*MONTHS
125NB=Units/12*MONTHS
126NL=Units/12*MONTHS
127NM=Units/12*MONTHS
128NS=Units/12*MONTHS
129NX=Units/12*MONTHS
130PR=Units/12*MONTHS
131PT=Units/12*MONTHS
132SL=Units/12*MONTHS
133SM=Units/12*MONTHS
134SP=Units/12*MONTHS
135SU=Units/12*MONTHS
136SX=Units/12*MONTHS
137TS=Units/12*MONTHS
138UB=Units/12*MONTHS
139UE=Units/12*MONTHS
140UF=Units/12*MONTHS
141UG=Units/12*MONTHS
142V1=Units/12*MONTHS
143V3=Units/12*MONTHS
144VL=Units/12*MONTHS
145VM=Units/12*MONTHS
146VS=Units/12*MONTHS
147WL=Units/12*MONTHS
148WM=Units/12*MONTHS
149WS=Units/12*MONTHS
150WW=Units/12*MONTHS
151YY=Units/12*MONTHS
CALC
YOU ARE AMAZING SIR!!!!! Thank you from the depths of my heart for solving this riddle! I love tricky formulas but this one definitely had me stumped! At least I learned some new tricks along the way. Thank you again!
 

Forum statistics

Threads
1,175,794
Messages
5,899,527
Members
434,779
Latest member
Mr1510

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
Top