Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Projecting Future Values / Advice (Excel XP)

    I've attached a spreadsheet with sample data and a comment in the cell where I need a formula. Any advice on how to accomplish the desired result OR advice to a more reasonable solution would be appreciated...

    What I have is 7 columns of sales figures, one for each day of the week. At the top of each column is a blank where I would like the formula (or resulting projections to appear). Each Tuesday, we would place Mondays actual sales to the end of the column. On Wednesday, we would add Tuesdays sales to the end of the Tuesday column, etc...

    I'm thinking that the projections formula would need to examine the last three numbers in the column to get a reasonable result.

    When the last three entries in a column are examined, there are 8 possible scenarios and each would need to be handled differently. As an example, the last entry might be a poitive number greater than zero, while the 2nd to the last entry could be a zero (holiday), while the 3rd to the last number would be greater than zero.

    I always seem to make things more complicated than they need to be, so I'm very interested in hearing of a different/better way.... I've been handed this assignment so that I could pass it along for others in the company to use for their data. IF not for that, I could do projections with pen and paper that would be fairly accurate.
    Attached Files Attached Files
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Projecting Future Values / Advice (Excel XP)

    Ricky

    [editted for the all-zero case]

    Use booleans in your formula like this

    =(10*E6+4*E7+2*E8)/(10*(E6<>0)+4*(E7<>0)+2*(E8<>0))

    The E6<>0 evaluates to either 1 or 0 and causes the divisor to adjust for the zero records.

    Then wrap it all in an IF statement to protect from all zeros

    =IF((E6+E7+E8)=0,"",(10*E6+4*E7+2*E8)/(10*(E6<>0)+4*(E7<>0)+2*(E8<>0)))

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Projecting Future Values / Advice (Excel XP)

    Thanks Andrew - Problem is that next as each day goes by, new numbers would be added to the end of each column. And I don't want to have to adjust the formulas each time a new entry is made. For the users sake, I want the program to self-adjust as new entries are made and then I can apply protection to the formulas. The end-user will simply enter daily sales in the appropriate columns. Then next weeks projections will calculate based on the most current set of entries... I told you I could complicate things...
    - Ricky

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Projecting Future Values / Advice (Excel XP)

    Ricky,

    The attachment show one approach. It has formulas added in Row 1, 2 and 4.
    The formulas are hidden with the custom number format: ";;;" (three semicolons).
    Andrew's formula (adjusted) is used in Row 3.
    Be aware that if the sales are zero, then a 0 must be filled in. Tthe Count function depends on this.

    Regards,

    Jim Cone
    San Francisco, CA

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Projecting Future Values / Advice (Excel XP)

    Jim

    Nice one. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Projecting Future Values / Advice (Excel XP)

    Thank you both, I especially like the three semi colon trick. I'll use that one til it crys uncle... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

Posting Permissions

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