+ Reply to Thread
Results 1 to 1 of 1

Dynamic Find and Divide

  1. #1
    Registered User
    Join Date
    New York, NY
    MS-Off Ver
    Excel 2007

    Red face Dynamic Find and Divide

    I am working with several hundred columns of data that is composed of totals and the breakdowns from those totals. The columns are sequential so I have a total, then a handful of columns that are a breakdown of that total (and sum to that total) and then a column that is another total, etc. I have written a simple formula that identifies when a column is a new total and when it is a subset of another total (false represents a new total). So all of all the “true” columns will sum up to the nearest false column to their left, until there is a new “false” column.

    Now I would like to calculate percentages for all of the numbers. So I need to be able to find the nearest corresponding total and divide it into the subtotal. For example, here is a mock of the data and what I want:

    Total 1 Subtotal 1 Subtotal 2 Total 2 Total 3 Subtotal 1 Subtotal 2 Subtotal 3
    USA 10 5 5 12 19 5 5 9
    Japan 15 6 9 11 22 11 8 3
    Mexico 5 4 1 8 13 3 6 4

    USA 100% 50% 50% 100% 100% 26% 26% 47%
    Japan 100% 40% 60% 100% 100% 50% 36% 14%
    Mexico 100% 80% 20% 100% 100% 23% 46% 31%

    The ideal solution would do the calculation in a new tab, so I can preserve the raw data and the percentages. It should also be noted I have hundreds of rows and not three like in this example. Example also attached. Thanks for any and all help![/SIZE][/FONT]
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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