Simplifying multiple formulae

S

Stclements

Board Regular
Joined
Aug 6, 2008
Messages
236
[TABLE="width: 1346"]
<colgroup><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD]






[/TD]
[TD]COLUMN I[/TD]
[TD][/TD]
[TD][/TD]
[TD]COLUMN L[/TD]
[TD][/TD]
[TD]COLUMN N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]ABF[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K55/100),100)[/TD]
[TD][/TD]
[TD]=IF(G55=1,K55*L55,IF(F55=1,K55*L55/Y55,K55*L55/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BATS[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K56/100),100)[/TD]
[TD][/TD]
[TD]=IF(G56=1,K56*L56,IF(F56=1,K56*L56/Y56,K56*L56/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BLT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K57/100),100)[/TD]
[TD][/TD]
[TD]=IF(G57=1,K57*L57,IF(F57=1,K57*L57/Y57,K57*L57/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DGE[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K58/100),100)[/TD]
[TD][/TD]
[TD]=IF(G58=1,K58*L58,IF(F58=1,K58*L58/Y58,K58*L58/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GSK[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K59/100),100)[/TD]
[TD][/TD]
[TD]=IF(G59=1,K59*L59,IF(F59=1,K59*L59/Y59,K59*L59/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RB[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K60/100),100)[/TD]
[TD][/TD]
[TD]=IF(G60=1,K60*L60,IF(F60=1,K60*L60/Y60,K60*L60/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RDSB[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K61/100),100)[/TD]
[TD][/TD]
[TD]=IF(G61=1,K61*L61,IF(F61=1,K61*L61/Y61,K61*L61/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SAB[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K62/100),100)[/TD]
[TD][/TD]
[TD]=IF(G62=1,K62*L62,IF(F62=1,K62*L62/Y62,K62*L62/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WTB[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K63/100),100)[/TD]
[TD][/TD]
[TD]=IF(G63=1,K63*L63,IF(F63=1,K63*L63/Y63,K63*L63/100))/100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CAT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K64/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G64=1,K64*L64,IF(F64=1,K64*L64/Y64,K64*L64/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]KO[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K65/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G65=1,K65*L65,IF(F65=1,K65*L65/Y65,K65*L65/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IBM[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K66/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G66=1,K66*L66,IF(F66=1,K66*L66/Y66,K66*L66/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JNJ[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K67/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G67=1,K67*L67,IF(F67=1,K67*L67/Y67,K67*L67/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MCD[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K68/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G68=1,K68*L68,IF(F68=1,K68*L68/Y68,K68*L68/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MRK[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K69/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G69=1,K69*L69,IF(F69=1,K69*L69/Y69,K69*L69/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MSFT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K70/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G70=1,K70*L70,IF(F70=1,K70*L70/Y70,K70*L70/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DIS[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MROUND($L$3/(K71/100),100)/100[/TD]
[TD][/TD]
[TD]=IF(G71=1,K71*L71,IF(F71=1,K71*L71/Y71,K71*L71/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SA40[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K72>$L$3,1,MROUND($L$3/K72,1))[/TD]
[TD][/TD]
[TD]=IF(G72=1,K72*L72,IF(F72=1,K72*L72/Y72,K72*L72/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NIKKEI[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K73>$L$3,1,MROUND($L$3/K73,1))[/TD]
[TD][/TD]
[TD]=IF(G73=1,K73*L73,IF(F73=1,K73*L73/Y73,K73*L73/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ASX[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K74>$L$3,1,MROUND($L$3/K74,1))[/TD]
[TD][/TD]
[TD]=IF(G74=1,K74*L74,IF(F74=1,K74*L74/Y74,K74*L74/100))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FTSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K75>$L$3,1,MROUND($L$3/K75,1))[/TD]
[TD][/TD]
[TD]=IF(G75=1,K75*L75,IF(F75=1,K75*L75/Y75,K75*L75/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DOW[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K76>$L$3,1,MROUND($L$3/K76,1))[/TD]
[TD][/TD]
[TD]=IF(G76=1,K76*L76,IF(F76=1,K76*L76/Y76,K76*L76/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INDIA[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K77>$L$3,1,MROUND($L$3/K77,1))[/TD]
[TD][/TD]
[TD]=IF(G77=1,K77*L77,IF(F77=1,K77*L77/Y77,K77*L77/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GOLD[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K78>$L$3,1,MROUND($L$3/K78,1))[/TD]
[TD][/TD]
[TD]=IF(G78=1,K78*L78,IF(F78=1,K78*L78/Y78,K78*L78/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BRENT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K79>$L$3,1,MROUND($L$3/K79,1))[/TD]
[TD][/TD]
[TD]=IF(G79=1,K79*L79,IF(F79=1,K79*L79/Y79,K79*L79/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K80>$L$3,1,MROUND($L$3/K80,1))[/TD]
[TD][/TD]
[TD]=IF(G80=1,K80*L80,IF(F80=1,K80*L80/Y80,K80*L80/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WHEAT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K81>$L$3,1,MROUND($L$3/K81,1))[/TD]
[TD][/TD]
[TD]=IF(G81=1,K81*L81,IF(F81=1,K81*L81/Y81,K81*L81/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SUGAR[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K82>$L$3,1,MROUND($L$3/K82,1))[/TD]
[TD][/TD]
[TD]=IF(G82=1,K82*L82,IF(F82=1,K82*L82/Y82,K82*L82/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP/ZAR[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K83>$L$3,1,MROUND($L$3/K83,1))[/TD]
[TD][/TD]
[TD]=IF(G83=1,K83*L83,IF(F83=1,K83*L83/Y83,K83*L83/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP/USD[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K84>$L$3,1,MROUND($L$3/K84,1))[/TD]
[TD][/TD]
[TD]=IF(G84=1,K84*L84,IF(F84=1,K84*L84/Y84,K84*L84/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP/YEN[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K85>$L$3,1,MROUND($L$3/K85,1))[/TD]
[TD][/TD]
[TD]=IF(G85=1,K85*L85,IF(F85=1,K85*L85/Y85,K85*L85/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP/EUR[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K86>$L$3,1,MROUND($L$3/K86,1))[/TD]
[TD][/TD]
[TD]=IF(G86=1,K86*L86,IF(F86=1,K86*L86/Y86,K86*L86/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AUD/HKD[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K87>$L$3,1,MROUND($L$3/K87,1))[/TD]
[TD][/TD]
[TD]=IF(G87=1,K87*L87,IF(F87=1,K87*L87/Y87,K87*L87/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]UK GILT[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K88>$L$3,1,MROUND($L$3/K88,1))[/TD]
[TD][/TD]
[TD]=IF(G88=1,K88*L88,IF(F88=1,K88*L88/Y88,K88*L88/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]US T BOND[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K89>$L$3,1,MROUND($L$3/K89,1))[/TD]
[TD][/TD]
[TD]=IF(G89=1,K89*L89,IF(F89=1,K89*L89/Y89,K89*L89/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BUND[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(K90>$L$3,1,MROUND($L$3/K90,1))[/TD]
[TD][/TD]
[TD]=IF(G90=1,K90*L90,IF(F90=1,K90*L90/Y90,K90*L90/100))*100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Above is a list of formulas that are need for each calculation within the same row. A lot are the same but there are also different ones, what I wonder is, is there away that these can be written in a simpler form so that the formulas will automatically calculate when the letters in the cells in column I are input.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

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
Looks like you have 6 formulas, 3 in each column. It would take some finagling, but I've found using Evaluate(Substitute(..)) in the name manager helps write out a couple of formulas and sub what is needed as necessary.
 
Upvote 0
There are essentially three formulas, I wonder if somehow giving each a number and corresponding that number with a name that it may then evaluate using the formula connected to name and number.
Thus if
[TABLE="class: cms_table, width: 1346"]
<tbody>[TR]
[TD]FTSE [/TD]
[TD][/TD]
[TD][/TD]
[TD](1)
(1) =IF(K75>$L$3,1,MROUND($L$3/K75,1)) [TABLE="class: cms_table, width: 1346"]
<tbody>[TR]
[TD](2)=IF(G75=1,K75*L75,IF(F75=1,K75*L75/Y75,K75*L75/100))
This would imply that FTSE has an inherent value of 1, In column L, if it sees FTSE in column I it would imply that it needs to select formula 1 and likewise if Column N reads that the corresponding cell in column I is FTSE then it too would select the formula 1 from its column of formulae and so on through all the names.[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Look up value
Replies
7
Views
585
Fluff
Fluff
E
  • Question Question
XIRR but values are from two different cell ranges
Replies
2
Views
1K
EthanP
E
P
  • Question Question
How do i simplify a sumifs function?
Replies
4
Views
351
Peter_SSs
Peter_SSs
K
  • Solved
Excel Percentage formulas and Pivot table chart displays
3 4 5
Replies
48
Views
3K
kellym9236
K
K
  • Solved
Incrementing cell address inside Filter function
2 3
Replies
20
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

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