+ Reply to Thread
Results 1 to 15 of 15

Calculating Proportional allocation using nested IF functions

  1. #1
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Calculating Proportional allocation using nested IF functions

    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)))))))))))))))))))))))))))))))))))

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    I'm pretty sure you could cut down the number of IF's by using the INDEX/ function more efficiently, and maybe replacing the VLOOKUP's with with INDEX/MATCH would help speed it up, but trying to "optimize" this beast without at least some sample data (with expected output) is/will be/would be a nightmare...not really sure I WANT to to try to recreate a WB to test all the possibilities here...

    If you could upload a small sample to show whats happening, it would definitely help
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    I am trying to load a scaled-down version of the file to demonstrate functionality. What is the file size limit? At the moment, the file is ~20MB

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    OUCH!!
    I believe the current limit is 1Mb

    Edit -
    I can scale formulas to larger ranges, just give me some idea of maximum row/column expectations
    Last edited by dredwolf; 11-17-2013 at 11:54 PM.

  5. #5
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    OK... so the file is attached. Let me talk you through the file set up and structure and that should also help understand the genesis of the logic for the formula.

    The first worksheet (Select) is where we have the ability to choose the name of the relevant teams as well as select what individual products will be Product 1, 2, 3 etc. For the purposes of this evaluation, I have de-identified all sensitive information. This sheet also contains the relative percentage breakdowns that the formula refers to for outputs i.e. after the formula assesses which combination of portfolio size and portfolio position, it returns a value from this sheet according to the named array.

    Secondly, we have Priority. This sheet allows us to select each product within different teams from a drop down list. We can then assign a value to each product, broken down into months over 6 years as a planning tool for our business, to understand what each teams portfolio would look like. Individual products can be sold by multiple teams at the same time.

    Finally there is Allocate. This is the calculated output for the purposes if this exercise. Depending upon the mix indicated on Priority, these cells will calculate the relative percentage each product is responsible for within each team (only one shown here). The sum of the percentage should be 100% (he notes somewhat parenthetically) and the individual results will vary depending upon how many products in the portfolio as well as individual circumstances of each team.

    Also, as an aside - there are pre-built macros in this sheet that have been disabled.

    Thanks for taking the time to look through this for me.
    Attached Files Attached Files

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    OK, this will take me a while, going to bed ATM, but I will get to it tomorrow,
    if someone else gets to it first, GREAT!

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    Okay came up with this in Allocate sheet C15:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down and across

    This cuts the level of if's from 35-8, so in worst case scenario, you are still only running 1/4 as may calculations per cell, a definite improvement!

    Hope this helps

  8. #8
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    Dredwolf,

    Nice solution there, incorporating the vlookup into the index function that returns the result. Given that I used similar process in other components of the formula, you'd think I'd be able to incorporate that too. Apparently not! Thanks for taking the time to look through it and find a more efficient way to solve the problem.

    There is one more layer of complexity to this problem that I did not address above and would be interested in your thoughts. We have space for up to 20 teams in the original spreadsheet and this formula is supposed to work for all of them. To this point, I have created 20 individual named arrays, one for each team, and I have needed to replace the arrays for each team when replicating the formula (e.g. Team2 for Team1 etc.). Is there a way to have a locked reference (e.g. $C$4) that resets for each team. To clarify, I will take this formula and copy it from the first range (Team 1 - see screen shot below) and into the relevant cells in the second range (Team 2 see screenshot below) and so on. The team name relative to each range is in the same place but a locked reference that will work within an individual range (i.e. column and row locked) then means that it will not be dynamic the way I want it to be when moving to the next range.

    Team 1.jpg
    Team 2.jpg

    I'd be interested to hear your thoughts.

    Rob

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    aaah... I believe I understand, depending on the fixed reference (team), the data displayed changes?

    You could use a drop down for the fixed reference, then INDIRECT function to get the appropriate team data into the formula, just not sure how it would affect the other references in the formula...if the locacions for the data changes with the team names, you may have to change the lookups as well when the team name changes, this may coplicate the formula to the point where your back to the long calculation times again !

    If you could upload something with a couple teams that we can test against, we may be able to find/figure something out, or this may turn out to be something that is just easier/faster using VBA

  10. #10
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    Take the spreadsheet I loaded a couple of posts ago. Copy and paste the entire section where the formula we have been discussing is present and replicate it below, say from row 58. There should now be two sections that resemble the screen shots I supplied earlier. Change the reference to =Select!$H$5 and this will change the team name to Team 2. Now, I would look to copy the formula above into these cells to calculate the relative proportion for each product in team 2. The issue here is that there are references to Team 1 and PP_Tm1 throughout the formula which is referencing a named array that has been defined as only Team 1 on the Priority sheet. This would need to be replaced in the current formula with PP_Tm2 to make the allocations returned accurate. I have now created a named array (Product_Priority_Combined) which is an array that contains all 20 team slots on the Priority page - I removed all but team 1 in the test workbook to try and reduce file size. Here is my progress on using and INDEX/MATCH function to eliminate the need for changing the named array.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have been working just on the first iteration to this point - would replicate this through the others when done. This formula still reveals the correct responses however it is referencing the locked cell $B$7 which returns the name of the team. What I would like to have happen is this cell to remain locked within each team array but then to change to the next team name when the formula is copied down into the next team's array. This reference would then be locked at this point for this array until it is moved to the next one and so on and so forth. I have experimented with using the OFFSET function however have not been able to work it to do what I need it to. your thoughts?

  11. #11
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    In this case, Warner Chilcott is Team 1

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    The way your table(s) are set up, you could assign ranges for the team, then use maybe a MOD function to get where to get the team name from....I think you would be better off using the formula supplied, and modify for each team, then you only have 20 sets of changes to make,the team name stuff, and where the data is accessed from, which for the most paqrt is a drag choose,copy paste, then edit drag copy...if we try to make this formula too dynamic, you are going to wind up with a bigger mess than the original formula
    (I personally, would rather do edits for 20 situations, than try to debug the monstrosity I'm envisioning this becoming) or maybe VBA, my VBA is a little on the low end, so I'll see if someone else wants to take a shot at a VBA solution for this

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    Still working on the new part, however, I did manage to further compact the base IF formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Now down to 5 levels

  14. #14
    Registered User
    Join Date
    11-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating Proportional allocation using nested IF functions

    I have had a thought. Given that there are named ranges for each team array, all I would need to do is to concatenate the word "Team" with a dynamic formula that returns the number equivalent to the team I want. Given that there is 56 rows of information contained in one team array, perhaps a formula like this would work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Adding 10 is to account for teams 11-20 being to the right of teams 1-10 and aligned to the same references e.g. Team 1 and Team 11 on the same rows
    The 50.4 denominator is on account of there being 504 rows in the spreadsheet across 10 teams and this ensures that the number of rows returns the accurate team number.

    If you know a more efficient approach, please share. Thanks again for your insight.

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Calculating Proportional allocation using nested IF functions

    if this works for you, I Say GREAT!
    as I have nothing better to offer, seems good to me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nested IF(OR Formula not calculating
    By djvybz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2013, 11:49 AM
  2. Nested IF Functions
    By Chemucla in forum Excel General
    Replies: 7
    Last Post: 10-14-2009, 04:15 PM
  3. nested functions
    By mortitia_uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2009, 08:39 AM
  4. Revenue allocation using combined/Nested IF statements
    By Irfan123 in forum Excel General
    Replies: 9
    Last Post: 07-28-2008, 09:29 AM
  5. nested if functions?
    By Struggling of Essex in forum Excel General
    Replies: 6
    Last Post: 12-29-2005, 04:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1