+ Reply to Thread
Results 1 to 9 of 9

Formula to calculate value of column at End of Month

  1. 05-10-2018, 07:44 PM #1
    nimv1
    nimv1 is offline
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Formula to calculate value of column at End of Month

    Hi there,

    I was hoping for some help to calculate the value of a column without going in and manually subtracting values!

    Please see attached for example.

    In the attached on sheet 1, I have the value of the whole of column A from sheet 2 under Apr 18

    For May 18, you will see I have the same, but went into sheet 2 and deducted the values where the exit month is May 18.

    Is there a way of doing this to automatically value the totals with deducting the value as each month occurs?

    Any questions, please do not hesitate to ask.

    Your help would be much appreciated.

    Regards

    N
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-10-2018, 08:21 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Formula to calculate value of column at End of Month

    Most of the dates in Sheet2 are not dates. They are text.

    If you are not aware of it dates are numbers. Formatting is cosmetic ... for human eyes. In the attached they are now dates and the headers in Sheet1 can now match that field.

    Then this formula in Sheet1 B2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  3. 05-10-2018, 08:33 PM #3
    nimv1
    nimv1 is offline
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Formula to calculate value of column at End of Month

    Hi Dave,

    Thanks for reply.
    Unfortunatley its not quite what I required. My apologies for not being clear initially.

    May is correct, but for June 18, I would want it to exclude June 18 and May 18, for Jul 18 I would want it to exclude Jul, Jun and May 18 and so on.

    I hope this is clear?

    Regards

    Nim
    Reply With QuoteRegister To Reply
  4. 05-10-2018, 08:35 PM #4
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Formula to calculate value of column at End of Month

    another way:
    with PowerQuery and PowerPivot

    Power Query for
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 05-10-2018, 09:24 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Formula to calculate value of column at End of Month

    I'm still not clear.

    Perhaps if you upload same workbook but with required results hand typed in I might be able to 'back-engineer' my understanding.
    Reply With QuoteRegister To Reply
  6. 05-10-2018, 09:46 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Formula to calculate value of column at End of Month

    In the meantime this is a wild guess. Try in C2 and fill across.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Apr-18
    May-18
    Jun-18
    Jul-18
    Aug-18
    Sep-18
    Oct-18
    Nov-18
    Dec-18
    Jan-19
    Feb-19
    2
    14,325,200.00
    13,140,200.00
    (711,200.00)
    401,200.00
    1,495,000.00
    (1,185,000.00)
    515,000.00
    (170,000.00)
    840,000.00
    -
    -
    Reply With QuoteRegister To Reply
  7. 05-11-2018, 01:56 AM #7
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,646

    Re: Formula to calculate value of column at End of Month

    Try in B2:

    Please Login or Register  to view this content.
    Drag accross.
    Quang PT
    Reply With QuoteRegister To Reply
  8. 05-11-2018, 05:41 AM #8
    nimv1
    nimv1 is offline
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Formula to calculate value of column at End of Month

    Dear All,


    I wanted to say many thanks for your help with the following formula in regards to my question (as per title).

    I have reopened as I realised another scenario may occur, and I needed your assistance again please

    For example, if an amount was added in September 18 (350,000 for example), how would I automate this?
    Therefore the amount in cell F2 would increase by this amount.

    Would it be easier if there was another column in sheet 2 with a entrance month?
    For example all of the current amounts having April 18 and then adding more as they occur?

    I have reattached the spreadsheet.
    Your help would be much appreciated.

    Regards

    N
    Last edited by nimv1; 05-11-2018 at 05:17 PM. Reason: Realised another scenario may occur
    Reply With QuoteRegister To Reply
  9. 05-11-2018, 05:18 PM #9
    nimv1
    nimv1 is offline
    Forum Contributor
    Join Date
    01-28-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    147

    Re: Formula to calculate value of column at End of Month

    Dear All,


    I wanted to say many thanks for your help with the following formula in regards to my question (as per title).

    I have reopened as I realised another scenario may occur, and I needed your assistance again please

    For example, if an amount was added in September 18 (350,000 for example), how would I automate this?
    Therefore the amount in cell F2 would increase by this amount.

    Would it be easier if there was another column in sheet 2 with a entrance month?
    For example all of the current amounts having April 18 and then adding more as they occur?

    I have reattached the spreadsheet.
    Your help would be much appreciated.

    Regards

    N
    Attached Files Attached Files
    Last edited by nimv1; 05-11-2018 at 07:21 PM.
    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] Formula for the number of Wednesdays in a month with Month as Column Heading (mmm)
    By roxdrob01 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-01-2018, 12:18 AM
  2. [SOLVED] Calculate total of a column depending on month / years
    By eAstro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-28-2016, 10:29 PM
  3. Need Formula to Calculate Sale Month Back to Production Month
    By Newbie403 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-13-2015, 05:28 PM
  4. [SOLVED] Calculate the sum of a column if the month and year is correct.
    By johansoder in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2014, 08:47 AM
  5. [SOLVED] Formula which can calculate how many days in a month and create a column for each day
    By moshjosh in forum Excel General
    Replies: 1
    Last Post: 12-09-2012, 06:01 PM
  6. How do I calculate the Value in a Column within a particular month?
    By Galtal in forum Excel General
    Replies: 4
    Last Post: 01-16-2012, 11:03 AM
  7. Calculate number of dates within a column based on Month
    By jpruffle in forum Excel General
    Replies: 6
    Last Post: 05-15-2009, 12:40 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1