• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Average sales by excluding all month of promotion in last 12 & 6 months

R

Rick1001

New Member
Hi all



can a formula be created which can go through all SKUs in column A than determine what were the months ,promotion ran from column C & D & than find average from Sales tab by excluding all sales from above calculated month range. result in column E & F & G



Both table will grow



Please note : The promotion month should not be counted in taking average
 

Attachments

  • Baseline.xlsx
    25.2 KB · Views: 6
Hi ,

Can you manually work out one example , for the SKU KS15F3 , and put in the values that should be displayed in the various cells against this SKU in the tab named Promotion , in the 3 output columns ?

Narayan
 
NARAYANK991 said:
Hi ,

Can you manually work out one example , for the SKU KS15F3 , and put in the values that should be displayed in the various cells against this SKU in the tab named Promotion , in the 3 output columns ?

Narayan
Click to expand...

Hi NARAYANK991 attched with example
 
Hi ,

You have shown 270 against the SKU ID in A8 ; the same SKU ID occurs in A25 , A76 , A93 , A106 , A113 , A121 , A125 and A152 ; what is supposed to be displayed in column E against these entries ?

You have shown 270 in column E ; what would be the outputs in columns F and G ?

Narayan
 
NARAYANK991 said:
Hi ,

You have shown 270 against the SKU ID in A8 ; the same SKU ID occurs in A25 , A76 , A93 , A106 , A113 , A121 , A125 and A152 ; what is supposed to be displayed in column E against these entries ?

You have shown 270 in column E ; what would be the outputs in columns F and G ?

Narayan
Click to expand...
Hi narayan

Please see attached
thanks for looking into this
 
Hi ,

See the attached file.

The file contains the sum at present ; let me know whether this is correct ; thereafter , let me know how the average should be calculated.

Narayan
 

Attachments

  • Copy of Baseline (1) (1).xlsx
    260.1 KB · Views: 4
NARAYANK991 said:
Hi ,

See the attached file.

The file contains the sum at present ; let me know whether this is correct ; thereafter , let me know how the average should be calculated.

Narayan
Click to expand...
Hi Narayan, we can calculate average by dividing 12 months sale by 12.
but there is on error
You have taken last 12 months and than excluded promo month sales
but
total number of non promo months should be 12 & 6 & 3

for example

if are running promo from Jan 17 to April 17 & we want to know 12 month sum or average than it should exclude JAN 17 - APRIL17 & take sales of last 12 months excluding 4 months. so non promo 12 month sale would be from JAN16 - DEC 16
 
Rick1001 said:
Hi Narayan, we calculate fixed average based on months
but there is on error
You have taken last 12 months and than excluded promo month sales
but
total number of non promo months should be 12 & 6 & 3

for example

if are running promo from Jan 17 to April 17 & we want to know 12 month sum or average than it should exclude JAN 17 - APRIL17 & take sales of last 12 months excluding 4 months. so non promo 12 month sale would be from JAN16 - DEC 16
Click to expand...
Hi ,

This will mean a complete reworking of everything.

Narayan
 
Hi ,

See if these figures are correct. Again , I am only talking of the SUM of the 12 months , 6 months and 3 months.

Narayan
 

Attachments

  • Copy of Baseline (1) (1).xlsx
    260.9 KB · Views: 8
You must log in or register to reply here.
Back
Top