Announcement

Collapse
No announcement yet.

Excel 2000-how To Share Costs Between 2 Partners

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel 2000-how To Share Costs Between 2 Partners

    My co-owner and I share equally all expenses incurred in running a boat .

    I would like to construct a spreadsheet showing the running totals incurred by each of us as we buy items and , at the same time , showing which one of us is ahead or behind on a fifty/fifty split on these transactions.

    I expect this will be necessary as one of us will be a more frequent purchaser of items than the other due to work commitments.

    Ideally I would like to have the relevant balance of costs showing automatically each time a cost is entered by either person.

    I have attempted to do this but have got bogged down by formula error messages which I don't understand.

    Any help would be appreciated.

  • #2
    Re: Excel 2000-how To Share Costs Between 2 Partners

    Originally posted by thenoo View Post
    My co-owner and I share equally all expenses incurred in running a boat .

    I would like to construct a spreadsheet showing the running totals incurred by each of us as we buy items and , at the same time , showing which one of us is ahead or behind on a fifty/fifty split on these transactions.

    I expect this will be necessary as one of us will be a more frequent purchaser of items than the other due to work commitments.

    Ideally I would like to have the relevant balance of costs showing automatically each time a cost is entered by either person.

    I have attempted to do this but have got bogged down by formula error messages which I don't understand.

    Any help would be appreciated.
    Oh I love math and formulas. Let me take a stab at it. Back in 30 minutes.

    Jas
    VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
    boche.net - VMware Virtualization Evangelist
    My advice has no warranties. Follow at your own risk.

    Comment


    • #3
      Re: Excel 2000-how To Share Costs Between 2 Partners

      That was pretty easy.

      http://www.boche.net/dropbox/Book1.xls
      Attached Files
      VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
      boche.net - VMware Virtualization Evangelist
      My advice has no warranties. Follow at your own risk.

      Comment


      • #4
        Re: Excel 2000-how To Share Costs Between 2 Partners

        Well done Jason.



        and now.....

        One of my peeves was provoked. In general this type of information is ill suited for a spreadsheet and should be in a database! (obviously in this case the issue is minor, don't mind me, Mr. thenoo)
        Read one of my all time favorite articles here, from Bloor Research.

        Disclaimer: I have no association with the above mentioned.

        Just my two cents.
        Last edited by Lior_S; 24th April 2007, 04:07.
        "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

        Comment


        • #5
          Re: Excel 2000-how To Share Costs Between 2 Partners

          Originally posted by Lior_S View Post
          Well done Jason.
          Thanks. Little known fact around here I'm sure... I have a college degree in Accounting/Tax. Early on I worked as an Accountant for a little over three years and decided (with the help and provoking of others) that my skills could be used in the IT industry.

          Originally posted by Lior_S View Post
          One of my peeves was provoked. In general this type of information is ill suited for a spreadsheet and should be in a database! (obviously in this case the issue is minor, don't mind me, Mr. thenoo)
          And now one of my pet peeves: Microsoft Access Databases that start out small and harmless, but soon turn into production applications that grow beyond their means, intended use, and support. Multiply this scenario by 2,200+ users over a time span of 10+ years and you end up with over 10,000 MS Access Databases that need to be ported to SQL.
          VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
          boche.net - VMware Virtualization Evangelist
          My advice has no warranties. Follow at your own risk.

          Comment


          • #6
            Re: Excel 2000-how To Share Costs Between 2 Partners

            Agreed.

            Now imagine all those little databases are spreadsheets.....
            Indeed a nightmare either way.
            "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

            Comment


            • #7
              Re: Excel 2000-how To Share Costs Between 2 Partners

              Originally posted by Lior_S View Post
              Agreed.

              Now imagine all those little databases are spreadsheets.....
              Indeed a nightmare either way.
              I think people understand much more clearly the limitations of a spreadsheet and when they've outgrown one. An Access datbase on the other hand they treat like an endless horn of plenty.

              Sometimes I think it was better when all we had was 640k of RAM.
              VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
              boche.net - VMware Virtualization Evangelist
              My advice has no warranties. Follow at your own risk.

              Comment


              • #8
                Re: Excel 2000-how To Share Costs Between 2 Partners

                Originally posted by jasonboche View Post
                Thanksfor the prompt reply.

                Unfortunately I don't understand the reply , including your thumbnail attachment, on the relevence to the problems detailed in my post.

                Perhaps I should have mentioned that I am a complete novice at EXCEL.

                I had thought of setting up a spreadsheet with headings ,say ,

                1.DATE
                2.AMOUNT SPENT ON BOAT
                3.AMOUNT SPENT BY PARTNER 1
                4.AMOUNT SPENT BY PARTNER 2

                At this point I got bogged down!

                I wanted here to have something which would show the running amount owed by either parner based on the criterion that this would be the comparison between half of the total in 2 and respective totals of 3 and 4.

                In other words , on any particular DATE , there would be automatically created a sum which would indicate which partner owed how much to the total kitty.

                Unfortunately this has far exceeded my ECEL skills and would be delighted to see whether this could be achieved.

                Comment


                • #9
                  Re: Excel 2000-how To Share Costs Between 2 Partners

                  Originally posted by thenoo View Post
                  Thanksfor the prompt reply.

                  Unfortunately I don't understand the reply , including your thumbnail attachment, on the relevence to the problems detailed in my post.

                  Perhaps I should have mentioned that I am a complete novice at EXCEL.

                  I had thought of setting up a spreadsheet with headings ,say ,

                  1.DATE
                  2.AMOUNT SPENT ON BOAT
                  3.AMOUNT SPENT BY PARTNER 1
                  4.AMOUNT SPENT BY PARTNER 2

                  At this point I got bogged down!

                  I wanted here to have something which would show the running amount owed by either parner based on the criterion that this would be the comparison between half of the total in 2 and respective totals of 3 and 4.

                  In other words , on any particular DATE , there would be automatically created a sum which would indicate which partner owed how much to the total kitty.

                  Unfortunately this has far exceeded my ECEL skills and would be delighted to see whether this could be achieved.
                  Easy enough. Just add a few IF statements in.

                  Workbook has been updated and new screenshot.

                  http://www.boche.net/dropbox/Book1.xls
                  Attached Files
                  VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
                  boche.net - VMware Virtualization Evangelist
                  My advice has no warranties. Follow at your own risk.

                  Comment


                  • #10
                    Re: Excel 2000-how To Share Costs Between 2 Partners

                    Originally posted by jasonboche View Post
                    Easy enough. Just add a few IF statements in.

                    Workbook has been updated and new screenshot.

                    http://www.boche.net/dropbox/Book1.xls
                    Thanks again and I appreciate the efforts you've taken here.

                    If you could print the formulae used in the IF statements in your included example it would be very helpful.

                    Sorry to be so dim about this - I'm just beginning to get used to EXCEL !

                    Comment


                    • #11
                      Re: Excel 2000-how To Share Costs Between 2 Partners

                      I provided the spreadsheet for you to download. I invite you to download it and take a look around. You're going to need to get familiar with office technologies if you're going to use a computer to help run a business. Excel is very powerful and can save you a lot of time rather than doing everything the old paper and pencil method with ledger paper.

                      Here are the formulas:

                      f8 =IF(G6=H6,"Partners are even steven",IF(G6>H6,"Partner 2 owes Partner 1","Partner 1 owes Partner 2"))
                      g5 =G6/G7
                      g6 =SUM(G11:G22)
                      g7 =G6+H6
                      g8 =IF(G6=H6,"",IF(G6>H6,G6-H6,H6-G6))
                      h5 =H6/G7
                      h6 =SUM(H11:H22)
                      VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
                      boche.net - VMware Virtualization Evangelist
                      My advice has no warranties. Follow at your own risk.

                      Comment


                      • #12
                        Re: Excel 2000-how To Share Costs Between 2 Partners

                        Originally posted by jasonboche View Post
                        I provided the spreadsheet for you to download. I invite you to download it and take a look around. You're going to need to get familiar with office technologies if you're going to use a computer to help run a business. Excel is very powerful and can save you a lot of time rather than doing everything the old paper and pencil method with ledger paper.

                        Here are the formulas:

                        f8 =IF(G6=H6,"Partners are even steven",IF(G6>H6,"Partner 2 owes Partner 1","Partner 1 owes Partner 2"))
                        g5 =G6/G7
                        g6 =SUM(G11:G22)
                        g7 =G6+H6
                        g8 =IF(G6=H6,"",IF(G6>H6,G6-H6,H6-G6))
                        h5 =H6/G7
                        h6 =SUM(H11:H22)
                        Thank you for the formulae and for your continuing help.

                        I apologise for the request for these formulae.

                        Unfortunately I missed the fact that you had supplied me with the URL for the example spreadsheet which, of course , included these formulae. I had merely clicked on the included thumbnail of this spreadsheet and , thus , failed to find the abilty to find these formulae for myself .

                        I'm sure all the help supplied by yourself will now enable me to find a solution to my problem.

                        Kind regards and appreciation for your replies.

                        Comment


                        • #13
                          Re: Excel 2000-how To Share Costs Between 2 Partners

                          Click image for larger version

Name:	untitled.jpg
Views:	1
Size:	107.2 KB
ID:	463128

                          showed this to a colleague at work, and his response:

                          "Damn, that's an expensive escort!"
                          ** Remember to give credit where credit is due and leave reputation points where appropriate **

                          Comment


                          • #14
                            Re: Excel 2000-how To Share Costs Between 2 Partners

                            So I guess your co-worker likes to go on the cheap when hiring escorts. The problem with that is it's a roll of the dice. You could end up with something looking like this:
                            Attached Files
                            VCDX3 #34, VCDX4, VCDX5, VCAP4-DCA #14, VCAP4-DCD #35, VCAP5-DCD, VCPx4, vEXPERTx4, MCSEx3, MCSAx2, MCP, CCAx2, A+
                            boche.net - VMware Virtualization Evangelist
                            My advice has no warranties. Follow at your own risk.

                            Comment

                            Working...
                            X