Delta Variances by Period

[MrRJ]

Hello,
I need your assistance. I have several columns, Period 1 through Period 13.
Every Period has specific data. I would like to create an analysis piece in
which I would like to see the delta change from say, Period 01 & Period 02
and the percentages change. I was thinking of using the Validation table so
the user can select what 2 Periods they want to see the anaylsis. It is from
there that I cannot think of a way to do...the If statement I was thinking
would exceed the max of 7. Any ideas?

MrRJ

 

[Bob Phillips]

Assuming that the data is in B2:B14, then the delta is

=INDEX(B2:B14,second_period)-INDEX(B2:B14,first_period)

the percentage change is then

=INDEX(B2:B14,second_period)/INDEX(B2:B14,first_period)-1

 

[Don]

I would create a separate sheet with the different compare / % and then
create a macro to select the compare you want and hide all others? This
assumes you would not want to disturbe the original sheet with all the
Periods?

good luck

 

[MrRJ]

Bob,
It looks good. Trying to visualize how this would work. As you stated, my
data is on row B, how does it look for what period it is? Let's say the word
"Period #" is on row A. I appreciate your helping me understand this.

MrRJ

 

[MrRJ]

Thanks Don,
Yes, I have thought of that too. I already have macros to hide/unhide
selected data for reporting purposes. Just trying to have other options.
That is why I was thinking of validation rule, meaning that every time you
select a validation, the cells would populate appropriately.

MrRJ

 

[Bob Phillips]

I was assuming that period 1 value is in B2, period 2 value in B3, etc., and
the first and second periods are simple numbers, so it indexes directly in.

What will you have in the DV selections?

 

[MrRJ]

Bob,
Not quite sure I understand here. Sorry. Here is a quick table so I can
reference our example. This area is B2:E3. If I put your formula in E3, how
does it know what periods to select? Also, what did you mean by DV
selections?

Period 1 Period 2 Period 3 Period 4
1,251,100 1,625,115 1,519,334 1,715,852

I appreciate your help in my understanding. Sometimes it is hard to
interpret via notes like this. I will get it.

MrRJ

 

[Bob Phillips]

You're not wrong about getting details across.

Sorry, but I am missing something here. In your first post you mentioned
that ... I was thinking of using the Validation table so the user can select
what 2 Periods they want to see the analysis ... So where are those two 2
periods identified, and will be of the form Period 1, Period 2, etc.

Assuming the above, and assuming the 2 periods identified are in M1 and M2,
then maybe this works

=INDEX($B$3:$E$3,MATCH($M$2,$B$2:$E$2,0))-INDEX($B$3:$E$3,MATCH($M$1,$B$2:$E$2,0))

and

=INDEX($B$3:$E$3,MATCH($M$2,$B$2:$E$2,0))/INDEX($B$3:$E$3,MATCH($M$1,$B$2:$E$2,0))-1

 

[MrRJ]

Thanks Bob. I will try this too. I found a way around this problem of mine.
What I did is I have 2 validation tables (one is the product and the other
is Period) in which the user needs to select. Behind the scenes, I
concatenated those fields and then I did a HLookup to find it. Lastly, I
simply put the delta formulas in the next two columns. Sounds messy huh?
Your way appears to be much simpler, however, will give it a shot.

Thanks, for you help Bob.