Feb 23 2021 04:00 AM
Hello.
So I have this task where I need to calculate after how many days on average each company pays their bills. Between the set date to pay the invoice and actual day when it's paid for each company. C collumn is date set to pay invoice, D is actual date when invoice was paid and E is companies.
Feb 23 2021 04:27 AM - edited Feb 23 2021 04:31 AM
@linards2000 Something like this?
I just did date due (column C)-date paid (column D). That will give me the the days paid per sale/invoice. Positive being paid before and negative being days late.
Then created a Pivot Table with the companies (column E) in the rows and the new calculation in the values and formatted to be an average. If you look at screenshot with the data you'll see the average matches. You could also format the pivot value as a whole number if you need a rounded value as in a whole day.
You can even see at the invoice level by adding in column A and then you can see how it will average with the total averaging in the subtotal. This isn't what you asked but you can from there customize the data and even search for a company via the filter in the Pivot.
Feb 23 2021 04:40 AM
I'd use a pivot table.
I added a column Dienų skaičius to the source data with formula =D2-C2.
The pivot table calculates the average of this column for each company.
See the attached version.
Feb 23 2021 06:56 AM
Feb 24 2021 12:35 AM