Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 394K Members
- 9,594 Online
- 426K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Accounting formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 01:21 AM

Thank you in advance.

Simply, Have an invoice tracking spreadsheet that is returning incorrect numbers.

Let's say column A is what is due, column B is what is paid and I need column C to provide what is still due. Problem is, sometimes we are given part payments which returns a false answer.

The current formula Calculates A as $5000, B as $3000 (part payment) and C as $0.00 were it should be $2000 is still due.

Obviously this is simplified, there are several columns summing similar numbers then charting the answer.

Labels:

- Tags:
- Formula's

14 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 03:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 01:46 PM

Sorry, I have simplified it to much. Actual formula =SUMIF(O2:O385,"<>",H2:H386) goes not help me if a part payment is received.

Column H is wages due. Columns F and G are the same but I calculate them separately (only way I know).

Column O is for payments received.

I will make sure any future questions are detailed.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 02:57 PM

@Old_School Could you post a small data sample (not 300 rows, just 10) and put in the expected results manually? It's hard to visualise what you want to achieve.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 03:30 PM

Column F Wage 1

Column G Wage 2

Column H Wage 3

Column I Total Wages

Column J Net amount

Column K Tax

Column L Total

Column M Payment date

Column N Amount paid

Column O Amount outstanding

I am trying to calculate how much is due to each person (columns F,G,H). If Column O is zero, then I do not want to calculate wages as they are already paid. Secondly, sometimes a part payment is made and it throws the calculations out. Ideally, If $10000 is due and $2000 is paid, then the $2000 would be divided equally reducing the amount due in wages.

Not a good start to my first question and I do apologise.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 03:34 PM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 05:13 PM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 09:47 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 09:59 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 10:10 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2019 11:25 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2019 12:59 AM

@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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2019 01:18 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2019 03:01 AM

@Old_School I don't know how to say it any more clearly. Please post a sample with the expected results filled in manually.

I really don't have a clue what your desired end state looks like.

Please post a version of your data sample that has this desired end state. You say you only want to calculate X if there is Y, but you don't say in WHICH CELLS you want to perform these calculations and what the result should be.

Please mock that up in your data sample, so I know what to aim for.

This is my last attempt at trying to coax the desired result out of you. If you cannot show me what you need to see IN A SPREADSHEET, I will have to abandon this question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2019 03:33 AM

Obviously my understanding of a community help forum is not what I thought. Sorry to waste your time.

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
89.5K
Views

4 Likes

28 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
18K
Views

20 Likes

65 Replies

*Updated 9/3* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
34.4K
Views

23 Likes

201 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
10.8K
Views

0 Likes

5 Replies

Microsoft Office 2019 Now Available – Comparing 2019 🆚 2016 🆚 365, New Features in Access & Excel

Dan Moorehead | PowerAccess
in
Access
on
09-24-2018
160K
Views

6 Likes

16 Replies

This form can't be distributed as it is asking for personal or sensitive information

NZLaSalle
in
Microsoft Forms
on
07-19-2019
5,400
Views

4 Likes

56 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft