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

 

[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

 

[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.