HI,
I have successfully created a formula that will achieve my aim. I am creating a simulation tool that will allow dexterity in planning processes for our sales organisation. We have a worksheet which covers from 2013-2018, calendarised by month. For each month in each team, we can select from our list of products and assign a value to that product for that month which represents its position in the product portfolio e.g. if given 1, that is the first product in the portfolio for that month; if given 2, it is the second and so forth. Depending upon the number of products in the lineup, the percentage allocation will vary depending on the position allocated i.e if only 1 product in lineup, then allocation is 100%; if there are 2 products it may be 70:30, if there are three it may be 55:35:10 etc. The other variable is which product is going into which team and so the formula would need to lookup what the product is to start with. This is the formula I developed, however it is large and is now causing the file to take ~5 minutes to open. I am interested if there is a simpler way to achieve this - have I complicated the formula too much? I am also interested if utilising VBA code would help (I am a novice with VBA) and if so, what would the code look like?
Some notes on the formula below:
- There are a number of named ranges referenced in this formula
- The position in the sales line up canc hange month by month so utilising a dynamic range in the vlookup components
- The basic logic behind the formula is that if Product X is position 1 AND there are X number of products in protfolio THEN X% allocation
- There are some specific anomalies for certain teams and years that are accounted for
- PCov is a named range that contains the % allocation breakdown - this allows simpler editing if the percentages change
=IF($B15="","",
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",C$1=2013,VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=2),INDEX(PCov,1,8),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",C$1=2013,VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=2),INDEX(PCov,3,8),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",C$1=2013,VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,1,9),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",C$1=2013,VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,2,9),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",C$1=2013,VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,3,9),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,1,10),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,2,10),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,3,10),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,1,11),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,2,11),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,3,11),
IF(AND(INDEX(Team1,1,2)="Warner Chilcott",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=4,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,4,11),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,1,12),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,2,12),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,3,12),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,1,13),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,2,13),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,3,13),
IF(AND(INDEX(Team1,1,2)="Menarini Primary Care",VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE)=4,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,4,13),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=1),INDEX(PCov,1,3),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=2),INDEX(PCov,1,4),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=2),INDEX(PCov,2,4),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,1,5),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,2,5),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=3),INDEX(PCov,3,5),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,1,6),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,2,6),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,3,6),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=4,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=4),INDEX(PCov,4,6),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=1,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=5),INDEX(PCov,1,7),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=2,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=5),INDEX(PCov,2,7),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=3,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=5),INDEX(PCov,3,7),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=4,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=5),INDEX(PCov,4,7),
IF(AND((VLOOKUP($B15,PP_Tm1,COLUMNS($C$5:H$5),FALSE))=5,COUNTIF(INDEX(PP_Tm1,0,COLUMNS($C$5:H$5)),"<>0")=5),INDEX(PCov,5,7),0)))))))))))))))))))))))))))))))))))
Bookmarks