If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Offset formula
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 |
#3
|
|||
|
|||
Offset formula
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 |
Thread Tools | |
Display Modes | |
|
|