Fix a range for DMAX for repeated applications

[chococ]

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!

[FDibbins]

You could probably do this with MAX(IF()), and maybe a helper column, but Please do not upload a picture ** your file...rather, upload a sample ** your workbook, showing what data you are working with, a few samples ** your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)