+ Reply to Thread
Results 1 to 4 of 4

Extracting data based on range

  1. #1
    Registered User
    Join Date
    06-24-2019
    Location
    Atlanta
    MS-Off Ver
    365
    Posts
    5

    Extracting data based on range

    I have a table for the number of products sold:

    Product Sold
    2020 2021 2022 2023
    A 1 3 5 6
    B 3 5 7 9
    C 1 3 4 5


    I have another table that calculates the cumulative product sold

    Culmulative Product Sold
    2020 2021 2022 2023
    A 1 4 9 15
    B 3 8 15 24
    C 1 4 8 13



    I have a price table based on # of product sold:

    Item Price
    <10 5
    <20 3
    <30 2
    <40 1


    What equation should I write to get the cost price of the product based on cumulative data? Basically, as the cumulative product for category A is less than 10 till 2022 the multiplier price is $5 but in 2023 as the cumulative product is more than 10 the multiplier is $3.

    Cost
    2020 2021 2022 2023
    A 5 15 25 18
    B 3 5 7 9
    C 1 3 4 5


    Please know that the actual data set has a price multiplier for multiple range and the number of products sold is in million. The above is just a sample.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,740

    Re: Extracting data based on range

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    06-24-2019
    Location
    Atlanta
    MS-Off Ver
    365
    Posts
    5

    Re: Extracting data based on range

    Apologies. Attached is the template.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,740

    Re: Extracting data based on range

    In C19
    Please Login or Register  to view this content.
    Fill all table

+ 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. Extracting information based on mutliple criteria and date range
    By Nweed in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2016, 05:06 PM
  2. Extracting text within a Cell range based on conditions.
    By Danielc1234 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-23-2016, 08:09 PM
  3. Extracting data from a range that changes daily based on a cells criteria
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-02-2014, 02:35 AM
  4. Extracting number of months from date range based on specific criterea
    By trianglehead in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2013, 07:27 PM
  5. extracting data from a range if a cell value is identical to a range name
    By blue_clouds_mountain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 05:28 PM
  6. Replies: 7
    Last Post: 05-31-2012, 11:24 AM
  7. Extracting data from a worksheet based on a range of in a column.
    By banderson@nwws.biz in forum Excel General
    Replies: 1
    Last Post: 02-23-2006, 12:15 PM

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