K. Georgiadis
02-19-2010, 12:50 PM
Long time no see! This may or not be a VBA issue:
I have a 6 column data table as follows
Column A: Name of crop
Column B: Name of Product
Column C: Net $ Sales of Product in that crop
Column D: Net Margin $ of Product in that crop
1) At bottom of Column D I sum up the net margin and list as total US $
2) I sort Column D so that the products with the highest margins are on top and those with the lowest at the bottom of the list
3) I add column E showing the % of total margin represented by each row ($ value of row divided by the sum total of Column D).
4) I add Column F showing the cumulative % of margin contribution, adding up to 100% at the last data row
Now here is my challenge: I want to have a variable criterion cell in which I can add a percentage, say 80%, with the intent being that no more data will be displayed once the cumulative margin contribution in column F reaches 80%, the idea being to filter out products that only contribute 20% of the total margin. The user should be able to set the criterion cell to any other percentage to display only the data set that meets the criteria.
I did not find this variable filtering option in Excel 2007's standard functions. Do you have any idea how this might be accomplished?
Thanks!
I have a 6 column data table as follows
Column A: Name of crop
Column B: Name of Product
Column C: Net $ Sales of Product in that crop
Column D: Net Margin $ of Product in that crop
1) At bottom of Column D I sum up the net margin and list as total US $
2) I sort Column D so that the products with the highest margins are on top and those with the lowest at the bottom of the list
3) I add column E showing the % of total margin represented by each row ($ value of row divided by the sum total of Column D).
4) I add Column F showing the cumulative % of margin contribution, adding up to 100% at the last data row
Now here is my challenge: I want to have a variable criterion cell in which I can add a percentage, say 80%, with the intent being that no more data will be displayed once the cumulative margin contribution in column F reaches 80%, the idea being to filter out products that only contribute 20% of the total margin. The user should be able to set the criterion cell to any other percentage to display only the data set that meets the criteria.
I did not find this variable filtering option in Excel 2007's standard functions. Do you have any idea how this might be accomplished?
Thanks!