+ Reply to Thread
Results 1 to 8 of 8

Formula to check if a product is on promotion and calculate average sales

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    24

    Formula to check if a product is on promotion and calculate average sales

    I have weekly sales data for a particular product. In the promotions tab if there is a 1 it means it is on promotion for that week. If the cell is blank then it wasn’t on promotion. In the promotions tab column AO I want to get the average sales for when it is on promotion.

    How do I get excel to identify the 1, when it is on promotion, and then average the sales of these weeks. I have multiple rows of data with common reference numbers.
    Attached Files Attached Files
    Last edited by CA_needing_help; 03-25-2015 at 07:28 PM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to check if a product is on promotion and calculate average sales

    Like this...

    =AVERAGEIF(C2:AK2,1,Sales!C2:AK2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Formula to check if a product is on promotion and calculate average sales

    Thanks for the reply. The problem is I have multiple rows of data with common reference numbers, and they aren't in the same rows on each tab

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to check if a product is on promotion and calculate average sales

    Sorry, not following you.

    Post a file with more data and tell us what result you expect.

  5. #5
    Registered User
    Join Date
    06-20-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Formula to check if a product is on promotion and calculate average sales

    Attached is the updated file. Cell AO3 contains the average sales for the 2 weeks that product 2 is on promo. Somehow need to look up the reference number in the sales promo tab, find out when the product is on promotion and then return the average sales only for the weeks it is on promotion
    Attached Files Attached Files

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to check if a product is on promotion and calculate average sales

    OK, now I see. We need to locate the correct row of the product.

    Try this...

    Entered in AO2 and copied down:


    =AVERAGEIF(C2:AK2,1,INDEX(Sales!C$2:AK$4,MATCH(A2,Sales!A$2:A$4,0),0))

  7. #7
    Registered User
    Join Date
    06-20-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Formula to check if a product is on promotion and calculate average sales

    Thanks for that. Works perfectly. Easy when you know how

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to check if a product is on promotion and calculate average sales

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Calculate Weighted Average using Sum product and Conditions
    By sathishpalaniswamy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-25-2014, 05:06 AM
  2. formula in Excel to calculate rolling daily average of sales total by date
    By Geekgurl in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-17-2014, 05:30 PM
  3. Product Sales to Month Sales
    By pelamis22 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-23-2013, 06:13 PM
  4. Formula to calculate multiple values from product sales
    By madscooter in forum Excel General
    Replies: 0
    Last Post: 06-16-2011, 12:53 PM
  5. formula to calculate sales tax from total sales
    By Deanna in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-05-2005, 04:05 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