Can do this without Macros?

[rs008f]

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 ****ed 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]

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 ****ed 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]

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]

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]

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]

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]

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]

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]

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