SLConsultant
New Member
- Joined
- Jun 8, 2015
- Messages
- 1
Hi, I have created the following formula and copied it to multiple rows & columns in a table in Excel which has greatly slowed down the recalculation time of the whole file. The purpose of the formula is to complete a staffing table based on a chosen a Staffing Method (so far I've only written the formula for 2 of the methods: Method 1 is based on a date to be hired and Method 6 is based on occupancy percentage thresholds) and the following variables:
The following variable is needed for all staffing methods:
Month End & Year (i.e. 11/30/2015) (which are the column headers in my table and there are currently 48 months to calculate for each position)
The following details are needed for Method 6 only:
Position Title (There are approximately 80 position titles, most of of which will use Method 1, approximately 10-20 positions will be staffed according to one of the other defined methods)
Level of Care (Method 6 refers to occupancy percentages for each month for a particular level of care)
=IFERROR(IF($D20=1,IF(M$17>$K20,$I20,0),IF($D20=6,INDEX(INDEX((_SMT1,_SMT2,_SMT3,_SMT4,_SMT5,_SMT6,_SMT7,_SMT8,_SMT9,_SMT10),,,IF($B20='KA-SMT'!$D$18,1,IF($B20='KA-SMT'!$E$18,2,IF($B20='KA-SMT'!$F$18,3,IF($B20='KA-SMT'!$G$18,4,IF($B20='KA-SMT'!$H$18,5,IF($B20='KA-SMT'!$I$18,6,IF($B20='KA-SMT'!$J$18,7,IF($B20='KA-SMT'!$K$18,8,IF($B20='KA-SMT'!$L$18,9,IF($B20='KA-SMT'!$M$18,10))))))))))),MATCH(ROUND(INDEX(M$4:M$13,MATCH($E20,$E$4:$E$13,0)),2),'KA-SMT'!$C$20:$C$119,0)),0)),0)
In this example I have 10 named ranges which represent the 10 possible position titles that use Staffing Method 6. I was hoping to not have to customize the formulas for each position title and I still need to add the other possible staffing methods and related logic into the formula! Are there some better ways to structure this? Should I use visual basic instead (and if so how?) or should I just turn off and on the recalculation of this tab in my file (and if so what is best method for accomplishing this)?
The following variable is needed for all staffing methods:
Month End & Year (i.e. 11/30/2015) (which are the column headers in my table and there are currently 48 months to calculate for each position)
The following details are needed for Method 6 only:
Position Title (There are approximately 80 position titles, most of of which will use Method 1, approximately 10-20 positions will be staffed according to one of the other defined methods)
Level of Care (Method 6 refers to occupancy percentages for each month for a particular level of care)
=IFERROR(IF($D20=1,IF(M$17>$K20,$I20,0),IF($D20=6,INDEX(INDEX((_SMT1,_SMT2,_SMT3,_SMT4,_SMT5,_SMT6,_SMT7,_SMT8,_SMT9,_SMT10),,,IF($B20='KA-SMT'!$D$18,1,IF($B20='KA-SMT'!$E$18,2,IF($B20='KA-SMT'!$F$18,3,IF($B20='KA-SMT'!$G$18,4,IF($B20='KA-SMT'!$H$18,5,IF($B20='KA-SMT'!$I$18,6,IF($B20='KA-SMT'!$J$18,7,IF($B20='KA-SMT'!$K$18,8,IF($B20='KA-SMT'!$L$18,9,IF($B20='KA-SMT'!$M$18,10))))))))))),MATCH(ROUND(INDEX(M$4:M$13,MATCH($E20,$E$4:$E$13,0)),2),'KA-SMT'!$C$20:$C$119,0)),0)),0)
In this example I have 10 named ranges which represent the 10 possible position titles that use Staffing Method 6. I was hoping to not have to customize the formulas for each position title and I still need to add the other possible staffing methods and related logic into the formula! Are there some better ways to structure this? Should I use visual basic instead (and if so how?) or should I just turn off and on the recalculation of this tab in my file (and if so what is best method for accomplishing this)?
Last edited:
