Can do this without Macros?

rs008f · Mar 11th, 2002, 02:17 PM

Below is an example of my spreadsheet.

Time 0 months (time required for profits to be earned)
Month 1 2 3 4 5 6 7 8 9 10 11 12
Profits($)10 20 30 40 50 60 70 80 90 100 110 120

My situation is as follows:
The profits depends of Time. If it is 5 months, then all the profits will be shited 5 months later; $10 would be appear under Month 6 and so on. Changing the # of Months will automatically shift the profits back and forth. What formula or tricks should I use to attain this. I tried using the IF function but it's too tedious and wasn't successful as it only allows 7 levels whereas there are 12 months(13 possibilities) in a year. Any advise would be great.

Steve Hartman · Mar 11th, 2002, 02:35 PM

Quote:

On 2002-03-11 13:17, rs008f wrote:
Below is an example of my spreadsheet.

Time 0 months (time required for profits to be earned)
Month 1 2 3 4 5 6 7 8 9 10 11 12
Profits($)10 20 30 40 50 60 70 80 90 100 110 120

My situation is as follows:
The profits depends of Time. If it is 5 months, then all the profits will be shited 5 months later; $10 would be appear under Month 6 and so on. Changing the # of Months will automatically shift the profits back and forth. What formula or tricks should I use to attain this. I tried using the IF function but it's too tedious and wasn't successful as it only allows 7 levels whereas there are 12 months(13 possibilities) in a year. Any advise would be great.

Let's do some assuming:

We're going to put the time month in A1 (5 in your ex)
Your month row is in row 2
Your profit is in row 3
put this formula in A3, =(A2-$A$1)*10 and copy down the row. Then use conditional formatting to change the font color to the background color whenever the cell is less than or = to 0. your profit values will "slide" down the scale depending on what value youo put in A1.

[ This Message was edited by: Steve Hartman on 2002-03-11 13:36 ]

[ This Message was edited by: Steve Hartman on 2002-03-11 13:41 ]

[ This Message was edited by: Steve Hartman on 2002-03-11 14:22 ]

rs008f · Mar 12th, 2002, 11:23 AM

What if the profits don't have a pattern? Like random numbers, this won't work as I have analyzed. Is there another way?

Steve Hartman · Mar 12th, 2002, 11:29 AM

Then why did you use an example with a pattern!?!?

Post some REAL data that has SOMETHING to do with what you REALLY want.

NateO · Mar 12th, 2002, 12:22 PM

How's this trickery:

If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits.

In cell b4 put:

=IF(B1=1,B3,0)

In cell c4 put:

=IF(B4>0,INDEX($B$3:$M$3,MATCH(B4,$B$3:$M$3,0)+1),IF(C2=$B$1,$B$3,0))

Copy c4 to d4:m4. Works for me, if I understand the question correctly.

Cheers,

Nate

P.S. - Holy rules by Chip! Feel like I'm back at boarding school!

[ This Message was edited by: nateo on 2002-03-12 12:36 ]

NateO · Mar 12th, 2002, 12:43 PM

I suppose you want a one month lag, if so, try this trickery:

If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits.

In cell b4 put:

=IF(B1=0,B3,0)

In cell c4 put:

=IF(B4>0,INDEX($B$3:$M$3,MATCH(B4,$B$3:$M$3,0)+1),IF(C2-1=$B$1,$B$3,0))

Copy c4 to d4:m4. Hope this helps.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 12:37 ]

NateO · Mar 12th, 2002, 01:55 PM

Sorry rsf008f, my last trickery doesn't work so well if you have the same value sporadically placed throughout your timeline. I think the following trickery will:

If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits.

In cell b4 put:

=IF(B1=0,B3,0)

In cell c4 put:

=IF(B4>0,INDEX($B$3:$M$3,COUNTIF($B$4:B4,"<>0")+1),IF(C2-1=$B$1,$B$3,0))

Copy c4 to d4:m4. Hope this helps.

Sorry about the goof. Cheers,

Nate

rs008f · Mar 12th, 2002, 02:36 PM

The last part works flawlessly. Thanks for helping me out. The combination you came up with, I wouldn't have figured it out. Do you know an Excel book that teaches this kind of techniques/tricks? I have the Microsoft Excel Book but it's like an extended help file, no much in combining such functions. Or is it only by experience can it be learnt?

NateO · Mar 12th, 2002, 02:40 PM

RS008,

That's excellent! My experience is based on experience and my need to play around with code and formulae. The internet's a good source (I've learned quite a bit from the good folks on this board), Jim Walkenbach might have some more advanced, practical books: http://j-walk.com/ss/books/xlbooks.htm
It appears Mr. Excel agrees: http://www.mrexcel.com/book.shtml These are mostly books by Jimmy. Like most things in life, I find no matter how much I read, I won't retain it unless I apply it...

Happy Hunting. Cheers,

Nate

[ This Message was edited by: nateo on 2002-03-12 14:29 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 02:17 PM	#1
rs008f New Member Join Date: Mar 2002 Location: Rochetser, NY Posts: 7	Below is an example of my spreadsheet. Time 0 months (time required for profits to be earned) Month 1 2 3 4 5 6 7 8 9 10 11 12 Profits($)10 20 30 40 50 60 70 80 90 100 110 120 My situation is as follows: The profits depends of Time. If it is 5 months, then all the profits will be shited 5 months later; $10 would be appear under Month 6 and so on. Changing the # of Months will automatically shift the profits back and forth. What formula or tricks should I use to attain this. I tried using the IF function but it's too tedious and wasn't successful as it only allows 7 levels whereas there are 12 months(13 possibilities) in a year. Any advise would be great.

Mar 12th, 2002, 11:23 AM	#3
rs008f New Member Join Date: Mar 2002 Location: Rochetser, NY Posts: 7	What if the profits don't have a pattern? Like random numbers, this won't work as I have analyzed. Is there another way?

Mar 12th, 2002, 11:29 AM	#4
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Then why did you use an example with a pattern!?!? Post some REAL data that has SOMETHING to do with what you REALLY want.

Mar 12th, 2002, 12:22 PM	#5
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	How's this trickery: If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits. In cell b4 put: =IF(B1=1,B3,0) In cell c4 put: =IF(B4>0,INDEX($B$3:$M$3,MATCH(B4,$B$3:$M$3,0)+1),IF(C2=$B$1,$B$3,0)) Copy c4 to d4:m4. Works for me, if I understand the question correctly. Cheers, Nate P.S. - Holy rules by Chip! Feel like I'm back at boarding school! [ This Message was edited by: nateo on 2002-03-12 12:36 ]

Mar 12th, 2002, 12:43 PM	#6
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	I suppose you want a one month lag, if so, try this trickery: If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits. In cell b4 put: =IF(B1=0,B3,0) In cell c4 put: =IF(B4>0,INDEX($B$3:$M$3,MATCH(B4,$B$3:$M$3,0)+1),IF(C2-1=$B$1,$B$3,0)) Copy c4 to d4:m4. Hope this helps. Cheers, Nate [ This Message was edited by: NateO on 2002-03-12 12:37 ]

Mar 12th, 2002, 01:55 PM	#7
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Sorry rsf008f, my last trickery doesn't work so well if you have the same value sporadically placed throughout your timeline. I think the following trickery will: If Time is in B1, months are in b2:m2 and profits are in b3:m3. Cells b4:m4 are time-adjusted profits. In cell b4 put: =IF(B1=0,B3,0) In cell c4 put: =IF(B4>0,INDEX($B$3:$M$3,COUNTIF($B$4:B4,"<>0")+1),IF(C2-1=$B$1,$B$3,0)) Copy c4 to d4:m4. Hope this helps. Sorry about the goof. Cheers, Nate

Mar 12th, 2002, 02:36 PM	#8
rs008f New Member Join Date: Mar 2002 Location: Rochetser, NY Posts: 7	The last part works flawlessly. Thanks for helping me out. The combination you came up with, I wouldn't have figured it out. Do you know an Excel book that teaches this kind of techniques/tricks? I have the Microsoft Excel Book but it's like an extended help file, no much in combining such functions. Or is it only by experience can it be learnt?

Mar 12th, 2002, 02:40 PM	#9
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	RS008, That's excellent! My experience is based on experience and my need to play around with code and formulae. The internet's a good source (I've learned quite a bit from the good folks on this board), Jim Walkenbach might have some more advanced, practical books: http://j-walk.com/ss/books/xlbooks.htm It appears Mr. Excel agrees: http://www.mrexcel.com/book.shtml These are mostly books by Jimmy. Like most things in life, I find no matter how much I read, I won't retain it unless I apply it... Happy Hunting. Cheers, Nate [ This Message was edited by: nateo on 2002-03-12 14:29 ]