Thanks:  0
Likes:  0

Thread: Can do this without Macros?

1. 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.

2. 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 ]

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

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

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

5. 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 ]

6. 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 ]

7. 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.

Nate

8. 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?

9. 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 ]

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•