+ Reply to Thread
Results 1 to 2 of 2

Fix a range for DMAX for repeated applications

  1. #1
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Fix a range for DMAX for repeated applications

    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!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,915

    Re: Fix a range for DMAX for repeated applications

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Calculating number of applications for the last week with dynamic range
    By huy_le in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2013, 09:09 PM
  2. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM
  3. How to send a range of cells in a sms text message by using VB -Applications in Excel
    By g00glethis1 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-06-2009, 06:32 PM
  4. [SOLVED] Printing text in a repeated cell/row that is longer than repeated
    By Valerie Dyet in forum Excel General
    Replies: 1
    Last Post: 02-12-2006, 11:30 PM
  5. [SOLVED] Printing from Excel. First row of range to be repeated
    By Tim Beechey-Newman, UK in forum Excel General
    Replies: 1
    Last Post: 08-15-2005, 06:05 AM

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