Excel formula needed - cell reference 1

[camster39]

I have a spreadsheet where each row corresponds to a specific month, by year, and there are 3 rows separating each year. The first month starts in Row 4.

Example:

Row 4 = January 2008
Row 5 = February 2008
Row 6 = March 2008
Row 7 = April 2008
Row 8 = May 2008
Row 9 = June 2008
Row 10 = July 2008
Row 11 = August 2008
Row 12 = September 2008
Row 13 = October 2008
Row 14 = November 2008
Row 15 = December 2008
Row 16 – Other data – should be ignored for this formula
Row 17 - Other data – should be ignored for this formula
Row 18 - Other data – should be ignored for this formula
Row 19 = January 2009
Row 20 = February 2009
Row 21 = March 2009
Row 22 = April 2009
Row 23 = May 2009
Row 24 = June 2009
Row 25 = July 2009
Row 26 = August 2009
Row 27 = September 2009
Row 28 = October 2009
Row 29 = November 2009
Row 30 = December 2009
Row 31 – Other data – should be ignored for this formula
Row 32 - Other data – should be ignored for this formula
Row 33 - Other data – should be ignored for this formula
Row 34 = January 2010
Row 35 = February 2010

In column G I have ‘monthly increase’ a currency value
In column H I have ‘monthly decrease’ a currency value

In cell N3 I have a numeric value.

I am looking to get a formula to put in column H which would take the current month in the year and subtract the value from N3 (as a month) to determine the G value.

Example assuming that N3 is 12:

H28 would be equal to G13 – October 2009 less 12 months equals October 2008
H30 would be equal to G15 – December 2009 less 12 months equals December 2008

Example assuming that N3 is 6:
H30 would be equal to G24 = December 2009 less 6 months equals June 2009.
H23 would be equal to G14 = May 2009 less 6 months equals November 2008

Example assuming that N3 = 9:

H30 would be equal to G22 = December 2009 less 9 months equals April 2009

I previously had an ‘OFFSET’ function that was working OK but I didn’t account for the 3 spaces in between each of the years. My ‘OFFSET’ also did not account for the fact that my first relevant row doesn’t start until Row #4. In other words it was taking Row 6 minus 9 and returning a negative row / error.

Is there any type of formula that I can use to accommodate this – even if I had to add some extra hidden columns and / or rows?

Thank you.

 

[Gavona]

To do this I put the date that the row related to in column A.
I entered a date in A4 then used a formula to copy down: in A5 the formula was =EOMONTH(A4,1)
I then used a formula to put the month number in column B. The formula in B4 was =MONTH(A4)

A working column could then contain the row offset from that cell. In row 24 the formula would be:
=IF((B24-$N$3)<1,$N$3+3,$N$3)

However I combined this with the offset formula so in H24 I had =OFFSET(H24,-IF((B24-$N$3)<1,$N$3+3,$N$3),-1)

This works for your examples but would not work if N3 was greater than 12. If necessary the approach could be adapted for that situation.



Gavin

 

[Gavona]

You will want to test it but I believe this works for any positive value in N3
=OFFSET(H24,-$N$3+3*ROUND((B24-$N$3)/12-0.5,0),-1)

Gavin

 

[camster39]

I think I'm doing something wrong:

Formatted columns A&B as Category Date with type 03/14/01. I entered 01/01/2009 in A4. In A5 I entered formula =EOMONTH(A4,1) and I'm getting #Name? as a result. And then the same type of error for column B since I messed up column A.

 

[Gavona]

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.

Gavin

 

[camster39]

Excellent - thank you very much.

I've got it working 'almost' perfectly and I neglected to tell you that my first row is January 2009. Here is my formula based on some slight movements of my rows.

=OFFSET(J19,-$P$3+3*ROUND((B19-$P$3)/12-0.5,0),-1)

The only problem I'm having is if my value in P3 is greater than the row returned in column I. For example if P3 = 13 and I'm in January 2010 then I get a #REF! as an answer asusming that my first row is January 2009. If I'm in February 2010 it works OK since it now goes back to January 2009. If I were to make the P3 value of 14 then I would get at #REF! for February 2010.

In other words, if my first row is January 2009 and I want the formula to apply to the entire J column. I could potentially have a value of 1 in P3 or a value of 100 in P3.

Thanks again...please let me know if I explained that OK.

 

[Gavona]

Glad it worked.

I understand the error but you have not explained the result that you want to see in that event.
Consider your first row of data:- That will always return #ref! because you want to get data relating to a previous month but that data is not in your table!

Gavin

 

[camster39]

In the event that the data is not present in the table can we return either a zero or a null? Row #1 would always have a zero and if $P$3 had a value of 2 or greater that would always have a zero etc...

I think that would do the trick as I have a ton of other cells that reference column J.

Thanks

 

[Gavona]

Have a look at the following functions and post back what you have tried (even if you solve your issue and don't need further help).
IF
ISERROR
ISREF

Gavin

 

[camster39]

I tried several versions on IF, ISREF, ISERROR and I'm either getting #Name? or just null values across the board.

=OFFSET(J19,-$P$3+3*ROUND((B19-$P$3)/12-0.5,0),-1)

I can find a workaround (e.g. not applying the formula to certain cells when P3 changes) if I can't get this working.

 

[Gavona]

Post back what you have tried" - I can't see any of those functions in the formula you posted - so I cannot help you to understand how to improve your approach.

Further hint:
If(condition, value if condition true, value if condition is false)
If(expression1 is an error,0,expression1)



Gavin

 

[camster39]

I tried something like this..

=IF(OFFSET(J20,-$P$3+3*ROUND((B20-$P$3)/12-0.5,0),-1)<0,OFFSET(J20,-$P$3+3*ROUND((B20-$P$3)/12-0.5,0),-1),0)

I didn't know what to put as my condition true so I just chose < 0. In reality if my offset value is #Name? then I want a zero otherwise I want the actual OFFSET value from my formula.

 

[Gavona]

If(iserror(offset(.....)),0,offset(.....))



Gavin

 

[camster39]

PERFECT!!!

Thank you very much for sticking with me on this and getting it working. Here is the final formula and it's working just as it should.

=IF(ISERROR(OFFSET(J19,-$P$3+3*ROUND((B19-$P$3)/12-0.5,0),-1)),0,OFFSET(J19,-$P$3+3*ROUND((B19-$P$3)/12-0.5,0),-1))

Thanks again.

 

[Gavona]

No problem - glad to help.

faq219-2884
Gavin