Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a very huge expression in one of my charts, and i have to edit it now.
The problem is that it is coming in one straight line and I have to scroll it horizontally.
I want it to be on the same page(wrapped) with a vertical scrollbar.
is there any other way except doing it manually?
Firstly, sorry for the huge emails everyone has just got 😄
Ok here is my own example:
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM([PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
I've put my finally bit at the top so you don't have to read the gumpf below if you don't want.
This took me about 10mins to do all of these tests with the help of google....
Finally I used notepad++ I hit enter at the end of the line and then hit the funny backwards P symbol on the toolbar which shows you the characters (Same as word)
To copy the CRLF I had to click to the left of it and then use shift + right arrow (once) to select it, but it didn't seem to work.
So a quick google later and I found you can do this in notepad++
Edit > Line operations > split lines
Select the line first, resize the window and then use that command and it will split the formula to fit the window
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL
STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER
SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY
COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL
CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST
DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL
STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE
SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_U
seMFF=1,[MFF],1)),SUM([PHARMACY
SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1
,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY
COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL
CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER
CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE
SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER
DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
One idea, use a JS Beautifier, it gets close but still not great as it will only pick up on } really
= round(((pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM([BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM(CNST / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM([EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM([ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([HIGH COST DRUGS]), SUM([IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM([MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM([OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM([PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM([PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM([PROSTHESES / IMPLANTS]), SUM([RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM([WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM([DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM([SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM([FCE NUMBER])) - (pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
CNST / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[HIGH COST DRUGS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PROSTHESES / IMPLANTS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[FCE NUMBER]))), 2)
Then I thought about using an excel beautifier (google excel formula beautifier) and I think this is OK but you'll need to tidy out the 'ARRAY ROW STOP weirdness where it has replaced the } bracket for some reason
=
round(
( ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
[BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[HIGH COST DRUGS]
),
SUM(
[IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OUTPATIENTS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OVERHEADS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PATHOLOGY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PHARMACY SERVICES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PROSTHESES/IMPLANTS]
),
SUM(
[RADIOTHERAPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[THERAPIES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[WARD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[DRUGS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[SECONDARY COMMISIONING] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
) /
SUM(
[FCE NUMBER]
) ) - ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [HIGH COST DRUGS]
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OUTPATIENTS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OVERHEADS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PATHOLOGY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PHARMACY SERVICES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PROSTHESES/IMPLANTS]
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [RADIOTHERAPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [THERAPIES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [WARD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [DRUGS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [SECONDARY COMMISIONING] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
) /
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [FCE NUMBER]
) ) ),
2
)
I guess copy and paste into notepad and then wrap your text?
Hello Chhavi
May be you can search the ",if" sentences and replace them with a "CRLF ,if".
You can copy the CRLF invisible character from notepad, for example.
That's what I would do.
Regards
Julian
No, this doesn't work.
With nested ifs like that I would personally do it manually (that's not that big!) that way you an add some nice indenting and make it easier for people to read
This was just an example i gave, the expression is really long, which cannot be done manually.
Paste the expression here and let see what can we do
=IF(SORT_COLUMN='Amount' and SORT_ORDER='Ascending' and Toggle_Switch=0,Num(ROUND(SUM({<Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)),'0'), IF(SORT_COLUMN='Amount' and SORT_ORDER='Ascending' and Toggle_Switch=1,Num(ROUND(SUM({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)),'0'), IF(SORT_COLUMN='Amount' and SORT_ORDER='Ascending' and Toggle_Switch=2,ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Ascending' and Toggle_Switch=0,ROUND(SUM(AGGR(sum({<CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Ascending' and Toggle_Switch=1,ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'}, CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Ascending' and Toggle_Switch=2,ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Ascending' and Toggle_Switch=0,(ROUND(ROUND(sum({<Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Ascending' and Toggle_Switch=1,(ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},CoCompanies={'*'}, DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Ascending' and Toggle_Switch=2,(ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='Amount' and SORT_ORDER='Descending'and Toggle_Switch=0,-Num(ROUND(SUM({<Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)),'0'), IF(SORT_COLUMN='Amount' and SORT_ORDER='Descending'and Toggle_Switch=1,-Num(ROUND(SUM({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)),'0'), IF(SORT_COLUMN='Amount' and SORT_ORDER='Descending' and Toggle_Switch=2,-ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount)), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Descending' and Toggle_Switch=0 ,-ROUND(SUM(AGGR(sum( {<CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Descending' and Toggle_Switch=1 ,-ROUND(SUM(AGGR(sum( {<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'}, CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='MKT Amount' and SORT_ORDER='Descending' and Toggle_Switch=2 ,-ROUND(SUM(AGGR(sum( {<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Descending' and Toggle_Switch=0,-(ROUND(ROUND(sum({<Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CoCompanies={'*'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Descending' and Toggle_Switch=1,-(ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop','Descendin Customer-Non Descendin Shop'},CoCompanies={'*'}, DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='%MKT Participation' and SORT_ORDER='Descending' and Toggle_Switch=2,- (ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'}, DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)), IF(SORT_COLUMN='Goal' and SORT_ORDER='Ascending' and Toggle_Switch=2,NUM(MAX({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} GOAL/100),'0.0%'), IF(SORT_COLUMN='%Variance' and SORT_ORDER='Ascending' and Toggle_Switch=2, (NUM((ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'}, DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)) -(ROUND(MAX({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} GOAL),0.0001)),'0.0%')), IF(SORT_COLUMN='Goal' and SORT_ORDER='Descending'and Toggle_Switch=2,-NUM(MAX({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} GOAL/100),'0.0%'), IF(SORT_COLUMN='%Variance' and SORT_ORDER='Descending'and Toggle_Switch=2, -(NUM((ROUND(ROUND(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} COMP_Amount)) /ROUND(SUM(AGGR(sum({<CNTRCT_FLG={'Descending Customer-Descending Shop'},CoCompanies={'*'}, DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>}COMP_Amount),PERIOD,Description of product,Client_identity,PRODUCT,Paytype))),0.0001)) -(ROUND(max({<CNTRCT_FLG={'Descending Customer-Descending Shop'},Description of product={'*'}-{'Others'},DATE={">=$(CurrQTR_Start)<=$(CurrQTR_End)"}>} GOAL),0.0001)),'0.0%')), IF(SORT_COLUMN='Goal' and SORT_ORDER='Ascending'and Toggle_Switch=0,'N/A', IF(SORT_COLUMN='Goal' and SORT_ORDER='Descending'and Toggle_Switch=1,'N/A', IF(SORT_COLUMN='%Variance' and SORT_ORDER='Ascending'and Toggle_Switch=0,'N/A', IF(SORT_COLUMN='%Variance' and SORT_ORDER='Descending'and Toggle_Switch=1,'N/A', ))))))))))))))))))))))))))
Firstly, sorry for the huge emails everyone has just got 😄
Ok here is my own example:
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM([PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
I've put my finally bit at the top so you don't have to read the gumpf below if you don't want.
This took me about 10mins to do all of these tests with the help of google....
Finally I used notepad++ I hit enter at the end of the line and then hit the funny backwards P symbol on the toolbar which shows you the characters (Same as word)
To copy the CRLF I had to click to the left of it and then use shift + right arrow (once) to select it, but it didn't seem to work.
So a quick google later and I found you can do this in notepad++
Edit > Line operations > split lines
Select the line first, resize the window and then use that command and it will split the formula to fit the window
=round(((pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL
STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER
SPECIAL PROCEDURE SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY
COMMISIONING'),SUM([BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM(CNST/If(v_UseMFF=1,[MFF],1)),SUM(if(v_CC=1,0,[CRITICAL
CARE]/If(v_UseMFF=1,[MFF],1))),SUM([EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM([ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM([HIGH COST
DRUGS]),SUM([IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM([MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM(if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM([OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER CLINICAL
STAFF]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM([OTHER SPECIAL PROCEDURE
SUITES]/If(v_UseMFF=1,[MFF],1)),SUM([OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM([OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM([PATHOLOGY]/If(v_U
seMFF=1,[MFF],1)),SUM([PHARMACY
SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM([PROSTHESES/IMPLANTS]),SUM([RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM([THERAPIES]/If(v_UseMFF=1
,[MFF],1)),SUM([WARD]/If(v_UseMFF=1,[MFF],1)),SUM([DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM([SECONDARY
COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM([FCE NUMBER])) - (pick(match(COST_TYPE,'BLOOD','CNST','CRITICAL
CARE','EMERGENCY','ENDOSCOPY','HIGH COST DRUGS','IMAGING','MEDICAL STAFF','NON CLINICAL INCOME','OPERATING THEATRES','OTHER
CLINICAL STAFF','OTHER DIAGNOSTICS','OTHER NON IDENTIFIABLE','OTHER SPECIAL PROCEDURE
SUITES','OUTPATIENTS','OVERHEADS','PATHOLOGY','PHARMACY
SERVICES','PROSTHESES/IMPLANTS','RADIOTHERAPY','THERAPIES','WARD','DRUGS','SECONDARY COMMISIONING'),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[BLOOD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}CNST/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_CC=1,0,[CRITICAL CARE]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[EMERGENCY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[ENDOSCOPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[HIGH COST DRUGS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[IMAGING]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[MEDICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}if(v_NCI=1,0,[NON CLINICAL
INCOME]/If(v_UseMFF=1,[MFF],1))),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OPERATING THEATRES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER CLINICAL STAFF]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER
DIAGNOSTICS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OTHER NON
IDENTIFIABLE]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[OTHER SPECIAL PROCEDURE SUITES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OUTPATIENTS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[OVERHEADS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[PATHOLOGY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[PHARMACY SERVICES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[PROSTHESES/IMPLANTS]),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[RADIOTHERAPY]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=,
'$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[THERAPIES]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=,
'$(v_local_5)'-=>}[WARD]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=,
'$(v_local_4)'-=, '$(v_local_5)'-=>}[DRUGS]/If(v_UseMFF=1,[MFF],1)),SUM({$<$TRUST={$(TrustB)}, '$(v_local_1)'-=, '$(v_local_2)'-=,
'$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[SECONDARY COMMISIONING]/If(v_UseMFF=1,[MFF],1)))/SUM({$<$TRUST={$(TrustB)},
'$(v_local_1)'-=, '$(v_local_2)'-=, '$(v_local_3)'-=, '$(v_local_4)'-=, '$(v_local_5)'-=>}[FCE NUMBER]))),2)
One idea, use a JS Beautifier, it gets close but still not great as it will only pick up on } really
= round(((pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM([BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM(CNST / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM([EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM([ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([HIGH COST DRUGS]), SUM([IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM([MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM(
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM([OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM([OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM([OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM([OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM([PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM([PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM([PROSTHESES / IMPLANTS]), SUM([RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM([THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM([WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM([DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM([SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM([FCE NUMBER])) - (pick(match(COST_TYPE, 'BLOOD', 'CNST', 'CRITICAL CARE', 'EMERGENCY', 'ENDOSCOPY', 'HIGH COST DRUGS', 'IMAGING', 'MEDICAL STAFF', 'NON CLINICAL INCOME', 'OPERATING THEATRES', 'OTHER CLINICAL STAFF', 'OTHER DIAGNOSTICS', 'OTHER NON IDENTIFIABLE', 'OTHER SPECIAL PROCEDURE SUITES', 'OUTPATIENTS', 'OVERHEADS', 'PATHOLOGY', 'PHARMACY SERVICES', 'PROSTHESES/IMPLANTS', 'RADIOTHERAPY', 'THERAPIES', 'WARD', 'DRUGS', 'SECONDARY COMMISIONING'), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[BLOOD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
CNST / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_CC = 1, 0, [CRITICAL CARE] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[EMERGENCY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[ENDOSCOPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[HIGH COST DRUGS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[IMAGING] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[MEDICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}
if (v_NCI = 1, 0, [NON CLINICAL INCOME] / If(v_UseMFF = 1, [MFF], 1))), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OPERATING THEATRES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER CLINICAL STAFF] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER DIAGNOSTICS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER NON IDENTIFIABLE] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OTHER SPECIAL PROCEDURE SUITES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OUTPATIENTS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[OVERHEADS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PATHOLOGY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PHARMACY SERVICES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[PROSTHESES / IMPLANTS]), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[RADIOTHERAPY] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[THERAPIES] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[WARD] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[DRUGS] / If(v_UseMFF = 1, [MFF], 1)), SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[SECONDARY COMMISIONING] / If(v_UseMFF = 1, [MFF], 1))) / SUM({
$ < $TRUST = {
$(TrustB)
},
'$(v_local_1)' -= ,
'$(v_local_2)' -= ,
'$(v_local_3)' -= ,
'$(v_local_4)' -= ,
'$(v_local_5)' -= >
}[FCE NUMBER]))), 2)
Then I thought about using an excel beautifier (google excel formula beautifier) and I think this is OK but you'll need to tidy out the 'ARRAY ROW STOP weirdness where it has replaced the } bracket for some reason
=
round(
( ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
[BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[HIGH COST DRUGS]
),
SUM(
[IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
[OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OUTPATIENTS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[OVERHEADS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PATHOLOGY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PHARMACY SERVICES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[PROSTHESES/IMPLANTS]
),
SUM(
[RADIOTHERAPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[THERAPIES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[WARD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[DRUGS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
[SECONDARY COMMISIONING] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
) /
SUM(
[FCE NUMBER]
) ) - ( pick(
match(
COST_TYPE,
'BLOOD',
'CNST',
'CRITICAL CARE',
'EMERGENCY',
'ENDOSCOPY',
'HIGH COST DRUGS',
'IMAGING',
'MEDICAL STAFF',
'NON CLINICAL INCOME',
'OPERATING THEATRES',
'OTHER CLINICAL STAFF',
'OTHER DIAGNOSTICS',
'OTHER NON IDENTIFIABLE',
'OTHER SPECIAL PROCEDURE SUITES',
'OUTPATIENTS',
'OVERHEADS',
'PATHOLOGY',
'PHARMACY SERVICES',
'PROSTHESES/IMPLANTS',
'RADIOTHERAPY',
'THERAPIES',
'WARD',
'DRUGS',
'SECONDARY COMMISIONING'
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [BLOOD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) CNST /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_CC = 1,
0,
[CRITICAL CARE] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [EMERGENCY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [ENDOSCOPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [HIGH COST DRUGS]
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [IMAGING] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [MEDICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) if(
v_NCI = 1,
0,
[NON CLINICAL INCOME] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OPERATING THEATRES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER CLINICAL STAFF] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER DIAGNOSTICS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER NON IDENTIFIABLE] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OTHER SPECIAL PROCEDURE SUITES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OUTPATIENTS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [OVERHEADS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PATHOLOGY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PHARMACY SERVICES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [PROSTHESES/IMPLANTS]
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [RADIOTHERAPY] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [THERAPIES] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [WARD] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [DRUGS] /
If(
v_UseMFF = 1,
[MFF],
1
)
),
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [SECONDARY COMMISIONING] /
If(
v_UseMFF = 1,
[MFF],
1
)
)
) /
SUM(
{ $ < $TRUST =
{ $(
TrustB
)
ARRAYROWSTOP)
ARRAYSTOP),
'$(v_local_1)' -
=
,
'$(v_local_2)' -
=
,
'$(v_local_3)' -
=
,
'$(v_local_4)' -
=
,
'$(v_local_5)' -
=
>
ARRAYROWSTOP)
ARRAYSTOP) [FCE NUMBER]
) ) ),
2
)
This works!!
Thank you so much