+ Reply to Thread
Results 1 to 13 of 13

Financial Formula Data Processing - multiple logic functions in one

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Financial Formula Data Processing - multiple logic functions in one

    So I have created a financial document where I can manage all of my income/expenses/bills and so fourth. I have been slowly upgrading the functions in this spreadsheet so that the data I end up with is more accurate and I'm not putting in every number and doing all of the calculations manually. There is one function or group of functions I am having difficulty figuring out. If you look at the example below I can explain everything.

    Example.xlsx

    So on the left, for every day to day expense I will document the date of purchase, the name of where the purchase was made, the category of that purchase, and how much. Now on the right I keep track of the expenses on a week to week basis. Here is what I am looking to do. I need to figure out a function that if I put an expense on the left side with the date, type and amount, the function will determine that depending on the date it will complete a function in the column of that week. Also within that week, depending on the type of expense it is, it will perform a SUM function within that week and put the total for each type of expense in the correct cell.

    So far, I have figured out one of the ladder logic SUMIF functions that depending on the type of expense in column C, the amount in column D will be added to the correct sell for that week. The part I can't figure out, is how to set it up so it can determine what week column it goes in based on the date.

    To use one of the entries in the example, on June 4th I went to the ATM and took out $60. The spreadsheet should be set up so it knows that June 4th is in the first week of the month, and the amount gets added to the ATM-Week1 cell in G4 because the type of expense is an ATM expense.

    Any help is good help Thank you in advance.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Financial Formula Data Processing - multiple logic functions in one

    Hi, and welcome to the forum

    Have you considered using a Pivot table to analyse and summarise your data in columns A:D.

    It's exactly what PTs were designed for so there's no point in using functions.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    Thank's for the reply Richard,

    I'm playing with the Pivot tables now and I have the basic setup of the summary table I manually created, but how do I set it up to function like the example I provided? Where it splits it up into each week and adds the sum of each category in each week?

    Thanks again,

    - Matt

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Financial Formula Data Processing - multiple logic functions in one

    Hello Matt,

    See the attached for one suggestion.

    Note I've added an extra column to show the week number. In practice this would be a formula that deduces the week # from the date alongside. I've made some of the entries show week 2 & 3 just to give you an idea.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    That's great, thank you so much for all of your help. The last question I have is about that logic function that determines the week based on the date. To be more specific the function would conclude a 1, 2, 3 or 4 depending on the date. Thanks again!

    - Matt

  6. #6
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    Now that I think about it, I'm also looking to have an average column next to the totals, is that something that can be included within the pivot table or do i have to set up the column outside of the table in the next column myself? if the ladder, I can easily do that part but I wasn't sure if it could be included in the table.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Financial Formula Data Processing - multiple logic functions in one

    Yes, that's correct.

    Assuming you mean a week number incrementing by 1 and continuing upwards then I'd do this by entering a base date in a cell somewhere and name it 'BaseDate'.

    Then in A2 enter
    =INT((B2-BaseDate)/7)+1
    and copy it down.

    Adjust the base date as necessary to obtain the right change in week number.

    Regards

  8. #8
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    What would the base date be? the 1st of the month? Sorry if I'm missing the obvious here. Also what about in the pivot table, having a column not only showing the total but the average for each type of expense as well?

    Thanks so much,

    - Matt

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Financial Formula Data Processing - multiple logic functions in one

    The base date would be whatever you need it to be in order to fit in with your definition of a week number. It all depends where your week starts. A week will increment every 7 days but until you state what the starting point is we don't know what dates are within week 1, or 2 or any week number. For instance is June 3rd - June 9th week 1 because the week starts on Sunday 3rd, or week 22 because the starting point is Jan 1st 2012?

    You can see the average for each type of expense by
    1. Clicking anywhere in the Pivot table so that you see the Pivot Table Field List
    2. In the Sum of Values quadrant in the bottom right of the Field window click the drop down arrow, select 'Value Field Settings and select Average instead of Sum.

    Regards

  10. #10
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    With the base date I have been judging the weeks in days of the month. So week 1 is the 1st to the 7th, week 2 is the 8th to the 14th and so on. The 4th week of the month is a little longer than the 7 days because not all months are eactly 28 days. For instance this month the 4th week would be the 22nd to the 30th.

    With the averages, I was able to see averages, but i'm looking to see it as a separate column next to the totals instead of just one or the other.

    Thanks again,

    - Matt

  11. #11
    Registered User
    Join Date
    06-09-2012
    Location
    NINB
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Financial Formula Data Processing - multiple logic functions in one

    I am very new here
    My Problem is that the Financial statement report generated from our database to excel is too complex to understand, takes as much as 3-5 pages
    What i want to achieve?: To automatically compress these reports to come out on a page each time i generate reports on Excel

    DATABASE———TO EXCEL———FINAL REPORT IN EXCEL(COMPRESSED)

    Key factors needed:
    Reports would always follow this same format from my database as shown in attachment (income statement link sheet with sheets A,B,C,D,E) excluding the details in CELLS H & I (colored in yellow and green)

    A click on CELL H coloured yellow shows the formulas and the details in CELL A and B that i summed/ merged up to get details cell H

    Cell H & I becomes the final outcome which stops on ROW 28 as compared to the initial report on cells A, B,D which as much as 104 ROW




    This is a preview of final template and how we want it, you should open the P&I Sheet

    Please can anybody help to get this with the click of a button in excel?
    Attached Files Attached Files

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Financial Formula Data Processing - multiple logic functions in one

    Phujo,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    06-08-2012
    Location
    Huntington, New York
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Financial Formula Data Processing - multiple logic functions in one

    Hi Richard,

    Just to clarify before I can mark this thread as solved (thanks to you), I have two questions.

    1) The pivot table is working great, but if I want to add a column next to the totals column, so they both show, not just one at a time, for an average column, can that be done or should I just make the column and set up the functions myself outside of the pivot table?

    2) The week column makes it great for organizing the pivot table per week, but you mentioned using a function to determine the week based on the date, and that I needed a base date. What would the base date be? I'm having a hard time figuring out the function to set up that base date. If you can use the month of June of this year as in the example that we have been talking about that would greatly help.

    Thanks so much,

    - Matt

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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