Dear all,
Sorry about the vague title, I know how to fix a range with $ but my problem is slightly more complicated.
Below I have attached a screenshot of a cleansed version of spreadsheet I am working with, as the numbers I am handling involves commercial confidential information. All values shown in the screenshot are plain text/values, in other words none of them hides a formula behind them.
Capture.PNG
What I need to do is, find the maximum market penetration percentage for each product, only if the customer base for that sub category is larger than 5000. Then I want to multiply that filtered maximum percentage by total market size (ie. find out the best possible target I could set), and deduct the size of current customer base from it (ie. find out how many more customers can I get).
At the moment I have came up with a DMAX formula as shown in the screenshot, which would do the job for one product. When I move to the next product I have to manually change the range plus add the headers before the formula would work. I have many products to go through, but if there could be a formula that I can copy and paste for each product I am happy to do that manually too. Just anything less tedious than my current method would be very much appreciated! Macro perhaps?
Thank you very much for reading, thanks in advance for any help!
Bookmarks