Excel: Trailing 3 month decline using data from a pivot table

Question

(http://stackoverflow.com/questions/5283466/calculate-moving-average-in-excel seems to be somewhat related. I'm curious to know the non-vba way to do this.)

My my pivot table has source data that creates a count of how many sales a person had in the past 36 days. Each day os 1 column in the pivot table, and the persons name is on the row.

Some of these people did not have sales on day1, day2, etc, and may only have 3 days where they sold something on days 14,15 and 16. No matter what their sequence, I want to to find the most recent sale data (closest to the right edge of pivot table) and calculate three sales increases e.g. C20/C19 will be >1 if they had more sales on the whatever day C20 is. The increase ca nbe fond by subtracting 1 and changing to percent. The problem is, if a person only had sales on d10, d11, d12 then how can I put a general formula in Excel to say "look for the most recent consecutive sales, then calculate this ratio"? For a person who had sales in the past three days, that's easy. It will be chaotic to hardcode where to look for each sales value.

     d1   d2   d3   d4...d7   d8   d9...d34  d35  d36   mostrecentincrease nextrecent
ant   1    5    7                                       7/5=1.4            5/1=5
bat                   ...10   11   12...                12/11=1.blah       11/10=1.1
cat   2    6    9   13
dog                                           19   20   20/19=1.blah       19/blank=0
elf   4                                                 4/dnexist=0

dog does not have 3 consecutive sales, do you want to compute the most recent increase based on last 2 consecutive sales? what if ant had a new sale on d36, but not in between, do you want to compute d36/d3 ? — Aprillion

Aprillion Aprillion · Accepted Answer · 2012-06-15T02:26:52

i don't have excel here, but i hope this will guide you to the correct result (use , instead of ; if that is your list separator):

define sales as a range of values from d1 to d36 for a given row (or use actual ranges)
compute positions of the last values for each row using these array formulas (use ctrl+shift+enter instead of just enter after you write these formulas):
position_1 =max(if(sales<>0;column(sales)))
position_2 =max(if((sales<>0)*(column(sales)<>position_1);column(sales)))
position_3 =max(if((sales<>0)*(column(sales)<>position_1)*(column(sales)<>position_2);column(sales)))
retrieve the values:
value_1 =index(sales;position_1)
do some error handling (=iferror(...;0)) and the like...

Excel: Trailing 3 month decline using data from a pivot table

1 Answers