+ Reply to Thread
Results 1 to 8 of 8

Self Ajusting cell , moving to provide target to meet end of year target

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    manchester
    MS-Off Ver
    Excel 2003
    Posts
    45

    Self Ajusting cell , moving to provide target to meet end of year target

    OK so my brain is not working this morning so need help please.

    I've a annual target to meet and I monitor each week, some weeks go over and some under. My annual target is below 3.74, however some weeks the result can go above so need to adjust the target each week to ensure the end of year target is met.

    I want to enter a formula that reviews all results entered and adjusts the target to bring back in line for end of year - any ideas as I can work it this morning

    If it helps I can put it into a sheet with cell enpty

    Thanks

    Andy
    Last edited by andycuk7; 02-15-2016 at 06:48 AM. Reason: Title change

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,046

    Re: Brain Freeze monday morning - help

    How about making your thread title a little bit more explicit of the problem and supplying a workbook?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,409

    Re: Brain Freeze monday morning - help

    1. It would help to see the workbook.

    Please change the title to a more appropriate one (according to the forumrules).

    After that:

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Brain Freeze monday morning - help

    I imagine the Moderators will want (insist) that you change your thread title to comply with forum rules.

    As Ali has said, be explicit and post a sample workbook. In principle, the adjusted weekly target would be the average of the annual target, less sales to date, averaged over the remaining weeks.

    I'd suggest you increase your annual target by, say, 10% to give you some room for manoeuvre if you have a bad week or two towards the end of the year. There might not be scope to cater for late under achievement.

  5. #5
    Registered User
    Join Date
    06-06-2012
    Location
    manchester
    MS-Off Ver
    Excel 2003
    Posts
    45

    Self Ajusting cell , moving to provide target to meet end of year target

    Hi, sorry if ive not met some rules. I've attached a simple sheet to show what im being asked to do.

    I want he cell to review the results already entered and then provide a live target to meet end of year target. I understand the live target will decrease if some results are above but cannot figure the formula out

    Thanks

    Andy
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,046

    Re: Brain Freeze monday morning - help

    Andy - you need to change the thread title of your FIRST post in the thread!!!

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,409

    Re: Self Ajusting cell , moving to provide target to meet end of year target

    You find the forumrules in the link below.

    http://www.excelforum.com/forum-rule...rum-rules.html

  8. #8
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Self Ajusting cell , moving to provide target to meet end of year target

    Can't post sample workbook - sorry.

    See if this helps:


    B
    C
    D
    E
    F
    3
    End of year target
    3.58
    Week Number
    1
    2
    4
    3.74
    Input Data
    1.57
    4.33
    5
    I need the cell above to provide an updated target to meet each week so by year end the end of year target is below 3.74
    Initial Target
    3.74
    3.74
    6
    Running Target
    3.74
    7.48
    7
    Achieved
    1.57
    4.33
    8
    Running Total
    1.57
    5.90
    9
    Running Average
    1.57
    2.95
    10
    Running Difference
    -2.17
    -1.58
    11
    Revised Target
    3.78
    3.77
    12
    13
    14
    Week Number
    E3
    week number as presented
    15
    Input Data
    E4
    weekly achievement data
    16
    Initial Target
    E5
    =IF(E$4="","",$B$4)
    17
    Running Target
    E6
    =IF(E$4="","",N(E$3)*N($B4))
    18
    Achieved
    E7
    =IF(E$4="","",N(E$4))
    19
    Running Total
    E8
    =IF(E$4="","",SUM($E$7:E$7))
    20
    Running Average
    E9
    =IF(E$4="","",SUM($E$7:E$7)/E$3)
    21
    Running Difference
    E10
    =IF(E$4="","",SUM($E$7:E$7)-N(E$3)*N($B4))
    22
    Revised Target
    E11
    =IF(E$4="","",(52*$B$4-(SUM($E$7:E$7)-N(E$3)*N($B4)))/52)
    23
    What you want …
    C3
    =INDEX($E$11:$BD$11,COUNTA($E$4:$BD$4))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Silly Excel Formula Question (having a brain freeze moment)
    By rjw524 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2015, 02:46 PM
  2. Brain Freeze or old age
    By bscs in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-15-2014, 11:17 PM
  3. Freeze Panes - Can I freeze a specific set of cells?
    By Rosco88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-15-2013, 09:17 AM
  4. [SOLVED] SUMIF brain freeze !!
    By andycuk7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2013, 06:01 AM
  5. [SOLVED] Find monday preceeding given-date unless date is a monday
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2012, 07:42 AM
  6. [SOLVED] Excel 2003 freeze panes won't freeze top row only
    By macbone2002 in forum Excel General
    Replies: 1
    Last Post: 05-31-2006, 11:10 AM
  7. Replies: 1
    Last Post: 04-13-2005, 06:35 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1