+ Reply to Thread
Results 1 to 4 of 4

Find % distribution based on data in lookup table

  1. 02-24-2020, 12:00 PM #1
    chullan88
    chullan88 is offline
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    427

    Find % distribution based on data in lookup table

    Hi,

    I have 6 items ( Their % of the total is shown in the table)

    These items will go in 5 boxes

    The items which will go in each box is shown in a lookup table

    Based on this lookup table, I need to generate a table which shows % distribution of each item in each box.

    I have done the calculations using helper tables and columns , but would like to receive a solution which will avoid usage of helpers.

    Sample file attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-24-2020, 12:17 PM #2
    KOKOSEK
    KOKOSEK is offline
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Find % distribution based on data in lookup table

    M
    N
    O
    P
    Q
    R
    2
    Box 1
    Box 2
    Box 3
    Box 4
    Box 5
    3
    		 **Oneplus
    3.50%
    3.50%
    0.00%
    3.50%
    3.50%
    4
    		 **Motorola
    2.20%
    2.20%
    2.20%
    2.20%
    2.20%
    5
    		 **Samsung
    0.00%
    0.00%
    11.00%
    11.00%
    0.00%
    6
    		 **Apple
    0.00%
    10.67%
    10.67%
    10.67%
    0.00%
    7
    		 **LG
    0.00%
    0.00%
    3.00%
    0.00%
    3.00%
    8
    		 **Huawei
    0.00%
    0.00%
    0.00%
    15.00%
    0.00%


    M
    N
    2
    Box 1
    3
    		 **Oneplus
    =SUMPRODUCT(($E$2:$I$2=N$2)*($E$3:$I$7=$M3))*VLOOKUP($M3,$B$2:$C$7,2,0)/SUM(1*($E$3:$I$7=$M3))

    Copy formula across range N3:R8
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Reply With QuoteRegister To Reply
  3. 02-24-2020, 12:25 PM #3
    chullan88
    chullan88 is offline
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    427

    Re: Find % distribution based on data in lookup table

    Thanks KOKOSEK !!
    It works well
    Reply With QuoteRegister To Reply
  4. 02-24-2020, 12:26 PM #4
    KOKOSEK
    KOKOSEK is offline
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Find % distribution based on data in lookup table

    Happy to help.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup 2 data points then into a table to find the the relevant cell
    By king10001 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-15-2019, 08:20 AM
  2. [SOLVED] lookup and return data from one table to another based on a cell reference.
    By Destiny1990 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2018, 07:50 PM
  3. Find and Replace based on lookup table
    By batchjb69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2017, 08:21 AM
  4. [SOLVED] Using A Formula To Lookup Data From Another Table Based On A Reference Column
    By TheBaileyBrew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2017, 09:41 PM
  5. Macro for data distribution on a table
    By Relapsed in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-07-2009, 06:40 AM
  6. How do I use lookup to find data based on 3 criteria?
    By jerickson in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-03-2008, 09:26 AM
  7. entering data based on a lookup table
    By rgdwar1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2008, 09:03 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1