+ Reply to Thread
Results 1 to 4 of 4

Need help with excel formulas to equally divide cost among given dates

  1. 01-19-2019, 03:55 PM #1
    abhineet.sabharwal
    abhineet.sabharwal is offline
    Registered User
    Join Date
    03-01-2013
    Location
    SLC,USA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Need help with excel formulas to equally divide cost among given dates

    Hello,

    I need help with excel formula to equally divide costs between given dates. I have have 2 part question:
    1) In sheet 1, I am showing activities in Column A, start dates in column B , finish dates in column C, cost in column D, and all the dates in column F to AG. I want to show the cost of the activity equally divided in the given duration of the activity when I enter the start date, finish date and Cost . Also, keep in mind these dates do not include weekends. Example: In sheet 1, Activity 1 starts on 12/10/18 and ends on 12/25/18. So it will automatically show 10 for each day from 12/10/18 to 12/25/18.
    2) Please refer to sheet 2. I want to show the same thing in weekly format compared to daily.

    Thank you for your help
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-19-2019, 04:54 PM #2
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Need help with excel formulas to equally divide cost among given dates

    Hello,

    Sheet1!E2 =NETWORKDAYS(B2,C2)
    Sheet1!F2 =IF(AND($B2<=F$1,$C2>=F$1),$D2/$E2,"")

    Copy across and down.

    Sheet2!F2 =SUMIFS(Sheet1!2:2,Sheet1!$1:$1,">="&F$1,Sheet1!$1:$1,"<"&F$1+5)

    Copy across and down.
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  3. 01-19-2019, 05:08 PM #3
    abhineet.sabharwal
    abhineet.sabharwal is offline
    Registered User
    Join Date
    03-01-2013
    Location
    SLC,USA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Need help with excel formulas to equally divide cost among given dates

    Thank you for yopur help!
    Reply With QuoteRegister To Reply
  4. 01-19-2019, 05:28 PM #4
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,618

    Re: Need help with excel formulas to equally divide cost among given dates

    You can put it on one sheet, too:
    Attached Files Attached Files
    Ben Van Johnson
    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. [SOLVED] Equally divide address into two columns from single column
    By adeel1 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-16-2017, 08:49 AM
  2. Is there a way to divide groups up equally?
    By taylorsm in forum Excel General
    Replies: 2
    Last Post: 05-27-2016, 10:46 AM
  3. Equally divide 0's and 1's in a Row of 8
    By Yash20 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-18-2015, 02:24 AM
  4. How to add group number to equally divide number of rows
    By neualex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2015, 03:34 PM
  5. Divide dates equally in a time period
    By Thistledown in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2014, 11:03 AM
  6. [SOLVED] Macro to divide a range and insert them in new rows equally
    By Netaji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 09:51 AM
  7. Divide cell amounts between cells equally
    By hotwoz in forum Excel General
    Replies: 6
    Last Post: 07-08-2011, 05:54 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