Forum Discussion
Accounting formula
Old_School Again, can you post a data sample? I mean a few rows of data with numbers and the expected result as a number as well. You can attach an Excel workbook when you post a reply. There is a "Choose Files" button below the reply text box.
Old_School I used my moderator powers to delete your last post and the attached file. The file clearly contained personal, confidential information that you don't want publicly available.
Again, please post a small data sample that illustrates what you would like to achieve. Don't post your whole workbook. I only need a few rows of data with numbers, then explain what the expected result should be.
Summing totals, how much each customer owes, average days to pay and so on is no problem. If column Q is $0.00, will not need to sum. If Q has a positive balance, then I need to add what is due for Columns F, G and H as they are liabilities until I am paid. Problem is, sometimes part payments are paid (cell O6), which then returns a false answer. Priority for that payment (cell O6) will always be divided equally to wages liability (Columns G and H). Once the remaining amount is paid, it will then bring back a $0.00 amount in Column Q, so will no longer need to be added anyway.
In summary, I need to sum what is due in Columns F, G and H if a balance is due in Column Q. Secondly, a part payment needs to be divided equally between G and H, reducing the amount due in each column by the equal share of the part payment.
Thank you for your patience.
Old_School Thanks for the sample file.
This is a sheet where each new row of data calculates based on the data above, right? You have a running total in columns P and Q.
I'm not sure I understand what you want to do with column G and H. The numbers in these cells are not calculated. Someone must have typed them in.
You have posted the table as it is now. That is a good starting point. Can you manually adjust the cells so I can see what you want to achieve? Then post that as well?
Yes Columns P and Q are self calculating but based on manual entries from Column O. Columns F,G and H are also manual entries inputted after an invoice is produced.
I only want to calculate what the total is for F,G and H if Column P has a balance outstanding. I need individual formulas for F, G and H placing the answer in any blank cell. The only variance would be if Column O has a part payment.
- That is the problem with explanations. The sample may not be representative of the whole. Thus, the querist is tempted to attach the actual file! Explaining something is both science and art. As the saying goes, if you cannot explain it, you cannot understand it yourself. That is a candid reality.
Sorry twifoo for not meeting your high standards. Can you replace 18 metres of purlins without taking the roof off? We all have our niche.
I will try and find a solution elsewhere without the criticism
- I am so sorry but I meant no offense to you. Just so you can explain what you mean, I advise you to do this:
Save your actual file as another name. Retain only 10 rows and delete all others. Replace the names of persons with generic names, like Alpha, Bravo, Charlie, Delta, and so forth.
That way, you will be able to clearly explain what you mean while maintaining confidentiality of your data.
