Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Harrisburg, Pennsylvania, USA
    Thanked 0 Times in 0 Posts

    adjust figures (Excel 2000)

    Hello All,

    This is an excerpt from a sheet I am trying to develop to forecast sales for a given period. The percents in cells D8 to J25 are an average of 5 week trend. The number in cell C9 is multiplied by these percents to give an average day,(CELLS D10 TO J 10), that the store would expect based on the 5 week historical data. What I want to be able to do is if I need to make a "Manual" correction to the % of business expected for any given day (cells C9 to J9) I would like the difference to be added to OTHER days of the week, not just the remainder, (ie) if the percent for Tues, is altered the remaining expected amount of 7.9% should be spread out to the remaining 6 days based on the percent of the entire week that the particular day contributes. The "Average percent of sales" is carried over from another sheet automatically. I had started playing w/ an If statement,(If d9=>.001c9*d9,d8) but I did not know how to add the differences from thee. I do not want to do this w/ a macro I would prefer to do this w/ formulas. Any suggestions????

    Thanks in advance for all the help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: adjust figures (Excel 2000)

    Try this formula in D10:

    =(IF(D9>0,D9,D8)+SUMPRODUCT(($D$9:$J$9>0)*(COLUMN( $D$10:$J$10)<>COLUMN(D10))*($D$8:$J$8-$D$9:$J$9)/6))*$C$9

    and fill right to J10.

Posting Permissions

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