Projecting Future Values / Advice (Excel XP)

Topic

New Reply

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.

Reply | Quote

Replies

Ricky

[editted for the all-zero case]

Use booleans in your formula like this

=(10*E6+4*E7+2*E8)/(10*(E60)+4*(E70)+2*(E80))

The E60 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*(E60)+4*(E70)+2*(E80)))

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

Jim

Nice one.

Reply | Quote

Thank you both, I especially like the three semi colon trick. I’ll use that one til it crys uncle…

Reply | Quote