FORMAT

[Tony7659]

Hi,
I have the table below for 2008/2009. Now, in another sheet in cell A1 I
have the "Year" and in cell B1 I have the "Period". I need a formula to find
the "Actual" value from the table below for the Period in cell B1. My issue
is that in my formula I may need let's say the value in cell B1 but 4 prior
periods and as you can see they may fall into the previous year (i.e.
A1=2009, B1=2; I need the actual value 4 prior periods or the value for
period 11, 2008).
Any ideas?
Thanks. Tony.

Year Period Actual
2008 1 $0.0000
2008 2 $0.0000
2008 3 $0.1500
2008 4 $0.2800
2008 5 $0.1500
2008 6 $0.2800
2008 7 $0.3000
2008 8 $0.4800
2008 9 $0.3600
2008 10 $0.0000
2008 11 $0.0000
2008 12 $0.0000
2009 1 $0.4200
2009 2 $0.4800
2009 3 $0.4800
2009 4 $0.5000
2009 5 $0.4800
2009 6 $0.4800
2009 7
2009 8
2009 9
2009 10
2009 11
2009 12

 

[NBVC]

Try something like:


Code:
--------------------
=INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2'!$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3)
--------------------


where Sheet2!A2:C25 contains your table and A1 on current sheet
contains 2009 and B1 contains 2...

 

[Tony7659]

NBVC,
It works. Just one more thing: would you please explain it to me? I need to
use this method in a variety of formulas so I would like to understand what I
am doing. Thank you for your time.
Tony.

 

[Bernard Liengme]

If A1 has the year and B1 the period, and we need to go back 3 periods ("4
periods before")
then we want the year given by =A1-(B1<5) which says subtract 1 from A1 if
B1 is less than 5)
and the period given by =B1-3+12*(B1<4) which says subtract 3 from B1 and
add 12 if B1 is less than 4
So since the table has only one entry for any given year/period we may use
SUMPROUDUCT for a two way lookup (or SUMIFS if we have Excel 2007)
I used this and it seems to do what you want:
=SUMPRODUCT(--(Sheet1!$A$2:$A$30=A1-(B1<5)),--(Sheet1!$B$2:$B$30=B1-3+12*(B1<4)),Sheet1!$C$2:$C$30)

Why did you use "Format" for the subject?
best wishes

 

[Bernard Liengme]

I like your solution better than mine
best wishes

 

[NBVC]

NBVC,
It works. Just one more thing: would you please explain it to me? I
need to
use this method in a variety of formulas so I would like to understand
what I
am doing. Thank you for your time.
Tony.

Index() function indexes the range you want to look at
Match() finds the position within a range that matches your lookup
criteria...

so:

=Index(Lookup_Range,Row,Column)

and can be substituted as follows

If the lookupTable is 1-dimensional, you don't need the Column
number... default to 1

=INDEX(Lookup_Table,MATCH(lookup_Value,Lookup_Range,0))

Since you have to columns that have to match criteria, we have to be
creative in the 2nd Match() argument above...

We use here, INDEX(('Sheet2'!$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0)
to create a lookup Range made up of an array of 1's and 0's, so that we
can Match a 1 against it to come up with a position.

The 2 conditions in the above Index() function return arrays of Trues
and Falses.. which, when multiplied together turn into 1's and 0's based
on multiplying TRUE*TRUE, TRUE*FALSE, FALSE*TRUE and FALSE*FALSE.. the
only one to give 1 is the TRUE*TRUE and that is when you have a match in
both columns (in same row)... The 0 at the end is because you need at
minimum to complete the Row Number condition of the Index() function for
it to work (and you really don't have one, so use 0)... The Match() part
then takes over and looks for a 1 in that array, returns the position of
that 1 and then the first INDEX() takes the corresponding item from the
same position vertically from Column C.

Hope that helps...

 

[Tony7659]

Bernard,
Thank you for replying. The formula is not giving me the results wanted. I'd
like to be able to go back any amount of periods from the one in B1 (even if
they fall in the year 2008 from the table) and get that value. I would also
love to understand the logic behind it. Thanks!
Tony.

 

[Bernard Liengme]

Send me (my private email fro my website or remove TRUENORTH from this on) a
sample file
I will explain my formula and the from NVBC which I prefer
best wishes

 

[Tony7659]

Thanks a lot. I had not received (or at least seen) the reply.
Tony.