Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Can do this without Macros?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Rochetser, NY
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    Mar 2002
    Location
    Rochetser, NY
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Rochetser, NY
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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