Ask Your Question

Dual Investor Sold Products Spreadsheet

asked 2019-08-07 00:17:17 +0200

ElectroIntellect gravatar image

updated 2020-08-02 02:59:31 +0200

Alex Kemp gravatar image

So I am trying to make a spreadsheet to track what is made on sold items with two investors. It needs to be designed in a way that two people can split the initial investment amount for a purchase, for example one pays $50 the other $150. I need a way so that until the initial investment capital is returned it splits the income inputted on one row next to the purchased item description and splits the income 50/50 until the lower of the two peoples investment amounts are reached. Then it needs to put 100% of the income towards the person with an amount remaining until both investors hit the break even line. Once it hits the break even line and becomes profit I need it to split in predetermined percentage such as 60/40.

A quick example Investor A pays $100 Investor B pays $200 This purchases lets say 10 items. The 10 items each sell for $40. So one item sells for $40 and would give Invest A/B a 50/50 split so $20 each. Same for item two, by item 3 it would only give investor A $10 and B gets the rest until 4 more sell and then they will have broken even. Once Investor A and B's total income minus investments is 0 (they both broke even) then all the profit amounts from that point forward need to split 60% for investor A and 40% for investor B.

Splitting the profits after they break even is simply the income total minus investment total then divided by percentages which is is easy. The part I am not entirely sure how to do is the income split 50/50 until one breaks even then 100% all towards the other until they both break even without doing some massive IF(AND(InvestorA<0,InvestorB<0),50/50split,IF(AND(InvestorB<0,InvestorA=0),All to InvestorB, IF(InvestorA<0, InvestorB=0, all to InvestorA and so on type of formula, which would work but is a pain to tweak later and it seems like there must be a better way to do this. If anyone knows a better way for me to do this it would be greatly appreciated, thanks for the help!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-08-07 08:13:11 +0200

lazarus477 gravatar image

Greetings ElectroIntellect!

Your question is a bloated one and not exactly of a technical nature but still a good question.

Perhaps instead of working with a solution based on a "massive" formula, you should instead take another approach. The approach I am suggesting is a change in mindset or philosophy rather than a technical solution.


Try to solve the problem by breaking it down into smaller components. Use individual cells, properly labeled, to hold each result. Then do a final calculation in another cell.

A general rule of thumb in computing: When a solution becomes to complex to keep track of, break it up into smaller components and work from there. This is called Divide And Conquer.

I hope this helps you to sort out your problem.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-07 00:17:17 +0200

Seen: 94 times

Last updated: Aug 07 '19