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

1. 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.

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

Like this...

=AVERAGEIF(C2:AK2,1,Sales!C2:AK2)

3. 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. 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. 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

6. 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. 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. Re: Formula to check if a product is on promotion and calculate average sales

You're welcome. Thanks for the feedback!

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

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