+ Reply to Thread
Results 1 to 3 of 3

Need Help with the formula to calculate target for next month based on actuals of previous

  1. 05-31-2018, 12:07 PM #1
    anu_chi
    anu_chi is offline
    Registered User
    Join Date
    05-31-2018
    Location
    Mumbai
    MS-Off Ver
    2016
    Posts
    4

    Need Help with the formula to calculate target for next month based on actuals of previous

    Hello,

    I have a requirement to track the hours saved each month. Let say my yearly target is 225678, simply, the monthly target is 18807 hours. However, if I don't meet a monthly target, then the next month target should be Overall Target-Last Month's Actual divided by rest of the months in column D. Also, in column K, if I have exceeded my target, I should get a message of "MET" (at the present I am getting the message as "NOT MET" as the exceed will be in minus).

    Sample sheet attached herewith.

    Regards,
    Anuroop
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-31-2018, 05:38 PM #2
    CAntosh
    CAntosh is offline
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Need Help with the formula to calculate target for next month based on actuals of prev

    Welcome to the forum!

    If I am understanding your goal correctly, try the following formula for D7:

    =IF([@TOTAL]>0,($K$3-SUM($I$6:$I6))/(COUNTA($I7:$I$18)),($K$3-SUM($I$6:$I6))/(13-MATCH(0,$I$7:$I$18,0)))

    And for K7, to fix your "NOT MET" issue:

    =IF([@TOTAL]>=[@[MONTHLY TARGET]],"MET","NOT MET")

    Fill both down the table and they should calculate correctly. The target for future months should adjust to account for the actual totals from months already completed. Take a look at the attachment to see if it is working as desired:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html
    Reply With QuoteRegister To Reply
  3. 06-01-2018, 03:33 AM #3
    anu_chi
    anu_chi is offline
    Registered User
    Join Date
    05-31-2018
    Location
    Mumbai
    MS-Off Ver
    2016
    Posts
    4

    Re: Need Help with the formula to calculate target for next month based on actuals of prev

    Bang On !

    Thank you so much. This is what I was looking for !

    Regards,
    Anuroop
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUM IF FORMULA BASED ON CURRENT/PREVIOUS/NEXT Month
    By AK123 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-21-2016, 01:10 PM
  2. [SOLVED] Dynamic formula to calculate the difference between current and previous month
    By kay007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2015, 11:19 AM
  3. [SOLVED] Formula for amount on previous month based on current month
    By Yu Marquez in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2014, 12:42 AM
  4. Formula to Calculate the Last Weekday of Previous Month
    By andrewc in forum Excel General
    Replies: 4
    Last Post: 08-26-2010, 07:32 AM
  5. Calculate running target by days in the month
    By Don in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] Calculate running target by days in the month
    By Robert in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] Calculate running target by days in the month
    By Robert in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-04-2005, 02:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1