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

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

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" wrote:

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,
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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

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

"Don" wrote:

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" wrote:

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

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?

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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,
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" wrote:

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?

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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" wrote:

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?

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

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.

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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" wrote:

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?

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

"Bob Phillips" wrote:

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



--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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