Offset formula

I've been trying to find an answer to a question I think I should know the
answer to with no luck. I hope someone can help me. I need to have a formula
change each month by moving down one row, and do it without using VBA so my
boss can tell where the number comes from (vba would make this a snap). I
have a formula in cell G5 (the 374.73 below) that needs to take a value in
cell E6 (the 18,612 below) minus the next month's number which is across from
Sept 08 this time (13,740) divided by the elapsed number of months since
August 07 to the next month. Right now I have this formula in cell G5 '=(E6-
E19)/COUNTIF(D646,"="&B1)' and this works fine for August, but when
September gets here the E19 part of this formula will need to be E20, unless
there's another way. The B1 in this formula is the end of month date of
8/31/2008. That will change to 9/30/2008 after the first of the month. The 13,
740 you see in cell E19 comes from the 13,740 under the heading of "value" to
the left side below. This changes monthly, so that next month the amount in
cell B11 is in E20. I had thought a vlookup using an offset formula would
work, but apparently they don't play well together. Looking at what I just
typed I know this is confusing, but I don't know what else to put. Any help
would be appreciated.

8/31/2008


ALG MARKET DATA - 2008
MODEL 2008 Chrysler 300 Period Value DIFF $374.73
MSRP $25,325 Aug-07 $18,612
INVOICE $23,658 Sep-07 $18,237 ($375) -2.0% 98.0%
Oct-07 $17,862 ($375) -2.1% 96.0%
39K MILES Nov-07 $17,487 ($375) -2.1% 94.0%
Term Value Dec-07 $17,113 ($375) -2.1% 91.9%
1 $13,740 1 Jan-08 $16,738 ($375) -2.2% 89.9%
2 $12,940 2 Feb-08 $16,363 ($375) -2.2% 87.9%
3 $12,185 3 Mar-08 $15,988 ($375) -2.3% 85.9%
4 $11,690 4 Apr-08 $15,614 ($375) -2.3% 83.9%
5 $11,590 5 May-08 $15,239 ($375) -2.4% 81.9%
6 $11,490 6 Jun-08 $14,864 ($375) -2.5% 79.9%
7 $11,400 7 Jul-08 $14,489 ($375) -2.5% 77.9%
8 $11,210 8 Aug-08 $14,115 ($375) -2.6% 75.8%
9 $10,805 9 Sep-08 $13,740 ($375) -2.7% 73.8%
10 $10,335 10 Oct-08 $12,940 ($800) -5.8% 69.5%
11 $9,910 11 Nov-08 $12,185 ($755) -5.8% 65.5%
12 $9,350 12 Dec-08 $11,690 ($495) -4.1% 62.8%
13 $8,730 13 Jan-09 $11,590 ($100) -0.9% 62.3%
14 $8,195 14 Feb-09 $11,490 ($100) -0.9% 61.7%
15 $7,700 15 Mar-09 $11,400 ($90) -0.8% 61.3%
16 $7,370 16 Apr-09 $11,210 ($190) -1.7% 60.2%
17 $7,300 17 May-09 $10,805 ($405) -3.6% 58.1%
18 $7,230 18 Jun-09 $10,335 ($470) -4.3% 55.5%
19 $7,170 19 Jul-09 $9,910 ($425) -4.1% 53.2%
20 $7,040 20 Aug-09 $9,350 ($560) -5.7% 50.2%
21 $6,770 21 Sep-09 $8,730 ($620) -6.6% 46.9%
22 $6,455 22 Oct-09 $8,195 ($535) -6.1% 44.0%
23 $6,165 23 Nov-09 $7,700 ($495) -6.0% 41.4%
24 $5,790 24 Dec-09 $7,370 ($330) -4.3% 39.6%
25 $5,380 25 Jan-10 $7,300 ($70) -0.9% 39.2%
26 $5,030 26 Feb-10 $7,230 ($70) -1.0% 38.8%
27 $4,705 27 Mar-10 $7,170 ($60) -0.8% 38.5%
28 $4,485 28 Apr-10 $7,040 ($130) -1.8% 37.8%
29 $4,440 29 May-10 $6,770 ($270) -3.8% 36.4%
30 $4,385 30 Jun-10 $6,455 ($315) -4.7% 34.7%
31 $4,340 31 Jul-10 $6,165 ($290) -4.5% 33.1%
32 $4,250 32 Aug-10 $5,790 ($375) -6.1% 31.1%
33 $4,070 33 Sep-10 $5,380 ($410) -7.1% 28.9%
34 $3,860 34 Oct-10 $5,030 ($350) -6.5% 27.0%
35 $3,660 35 Nov-10 $4,705 ($325) -6.5% 25.3%
36 $3,405 36 Dec-10 $4,485 ($220) -4.7% 24.1%

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

Your layout is a little hard to follow (the spacing got crowded), but let me
ask you this... is the E19 currently in your formula (and what you want to
be E20 on September 1st) the date for the last day of the current month? If
so, you should be able to simply replace E19 in your formula with this...

DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

and it will automatically calculate the last day of the month for the
current month for you.

Rick


"Joe_Hunt" u45578@uwe wrote in message news:89507f1c4927b@uwe...
I've been trying to find an answer to a question I think I should know the
answer to with no luck. I hope someone can help me. I need to have a
formula
change each month by moving down one row, and do it without using VBA so
my
boss can tell where the number comes from (vba would make this a snap). I
have a formula in cell G5 (the 374.73 below) that needs to take a value in
cell E6 (the 18,612 below) minus the next month's number which is across
from
Sept 08 this time (13,740) divided by the elapsed number of months since
August 07 to the next month. Right now I have this formula in cell G5
'=(E6-
E19)/COUNTIF(D646,"="&B1)' and this works fine for August, but when
September gets here the E19 part of this formula will need to be E20,
unless
there's another way. The B1 in this formula is the end of month date of
8/31/2008. That will change to 9/30/2008 after the first of the month. The
13,
740 you see in cell E19 comes from the 13,740 under the heading of "value"
to
the left side below. This changes monthly, so that next month the amount
in
cell B11 is in E20. I had thought a vlookup using an offset formula would
work, but apparently they don't play well together. Looking at what I just
typed I know this is confusing, but I don't know what else to put. Any
help
would be appreciated.

8/31/2008


ALG MARKET DATA - 2008
MODEL 2008 Chrysler 300 Period Value DIFF $374.73
MSRP $25,325 Aug-07 $18,612
INVOICE $23,658 Sep-07 $18,237 ($375) -2.0% 98.0%
Oct-07 $17,862 ($375) -2.1% 96.0%
39K MILES Nov-07 $17,487 ($375) -2.1% 94.0%
Term Value Dec-07 $17,113 ($375) -2.1% 91.9%
1 $13,740 1 Jan-08 $16,738 ($375) -2.2% 89.9%
2 $12,940 2 Feb-08 $16,363 ($375) -2.2% 87.9%
3 $12,185 3 Mar-08 $15,988 ($375) -2.3% 85.9%
4 $11,690 4 Apr-08 $15,614 ($375) -2.3% 83.9%
5 $11,590 5 May-08 $15,239 ($375) -2.4% 81.9%
6 $11,490 6 Jun-08 $14,864 ($375) -2.5% 79.9%
7 $11,400 7 Jul-08 $14,489 ($375) -2.5% 77.9%
8 $11,210 8 Aug-08 $14,115 ($375) -2.6% 75.8%
9 $10,805 9 Sep-08 $13,740 ($375) -2.7% 73.8%
10 $10,335 10 Oct-08 $12,940 ($800) -5.8% 69.5%
11 $9,910 11 Nov-08 $12,185 ($755) -5.8% 65.5%
12 $9,350 12 Dec-08 $11,690 ($495) -4.1% 62.8%
13 $8,730 13 Jan-09 $11,590 ($100) -0.9% 62.3%
14 $8,195 14 Feb-09 $11,490 ($100) -0.9% 61.7%
15 $7,700 15 Mar-09 $11,400 ($90) -0.8% 61.3%
16 $7,370 16 Apr-09 $11,210 ($190) -1.7% 60.2%
17 $7,300 17 May-09 $10,805 ($405) -3.6% 58.1%
18 $7,230 18 Jun-09 $10,335 ($470) -4.3% 55.5%
19 $7,170 19 Jul-09 $9,910 ($425) -4.1% 53.2%
20 $7,040 20 Aug-09 $9,350 ($560) -5.7% 50.2%
21 $6,770 21 Sep-09 $8,730 ($620) -6.6% 46.9%
22 $6,455 22 Oct-09 $8,195 ($535) -6.1% 44.0%
23 $6,165 23 Nov-09 $7,700 ($495) -6.0% 41.4%
24 $5,790 24 Dec-09 $7,370 ($330) -4.3% 39.6%
25 $5,380 25 Jan-10 $7,300 ($70) -0.9% 39.2%
26 $5,030 26 Feb-10 $7,230 ($70) -1.0% 38.8%
27 $4,705 27 Mar-10 $7,170 ($60) -0.8% 38.5%
28 $4,485 28 Apr-10 $7,040 ($130) -1.8% 37.8%
29 $4,440 29 May-10 $6,770 ($270) -3.8% 36.4%
30 $4,385 30 Jun-10 $6,455 ($315) -4.7% 34.7%
31 $4,340 31 Jul-10 $6,165 ($290) -4.5% 33.1%
32 $4,250 32 Aug-10 $5,790 ($375) -6.1% 31.1%
33 $4,070 33 Sep-10 $5,380 ($410) -7.1% 28.9%
34 $3,860 34 Oct-10 $5,030 ($350) -6.5% 27.0%
35 $3,660 35 Nov-10 $4,705 ($325) -6.5% 25.3%
36 $3,405 36 Dec-10 $4,485 ($220) -4.7% 24.1%

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

That'll work. Thank you very much!

Rick Rothstein (MVP - VB) wrote:
Your layout is a little hard to follow (the spacing got crowded), but let me
ask you this... is the E19 currently in your formula (and what you want to
be E20 on September 1st) the date for the last day of the current month? If
so, you should be able to simply replace E19 in your formula with this...

DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

and it will automatically calculate the last day of the month for the
current month for you.

Rick

I've been trying to find an answer to a question I think I should know the
answer to with no luck. I hope someone can help me. I need to have a
[quoted text clipped - 69 lines]
35 $3,660 35 Nov-10 $4,705 ($325) -6.5% 25.3%
36 $3,405 36 Dec-10 $4,485 ($220) -4.7% 24.1%

--
Message posted via http://www.officekb.com