Calculate payment over/shortage

Discuss the spreadsheet application

Calculate payment over/shortage

Postby RusselB » Fri Dec 06, 2019 6:09 am

IN the attached spreadsheet, in column F there is a calculation for the minimum payment due in the following month.
What I've having difficulty with is calculating the amount paid up to and including the 20th day of that month, and then comparing that to the amount that was supposed to be paid, showing what, if any, overage or shortage in payment occurred.
I'd like this in column G.
Since the dates and amounts seemed relevant to solving this, I didn't anonymize that data.

My preference is a solution for OpenOffice, though I do have both Open and LibreOffice installed.
Attachments
CC.ods
(24.79 KiB) Downloaded 15 times
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5794
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calculate payment over/shortage

Postby MrProgrammer » Sun Dec 08, 2019 2:20 am

RusselB wrote:What I've having difficulty with is calculating the amount paid up to and including the 20th day of that month, and then comparing that to the amount that was supposed to be paid, showing what, if any, overage or shortage in payment occurred.
I don't fully understand how one knows which amounts to include. If it's the amounts in the period to the following 20th of the month =DATE(YEAR(A3);MONTH(A3)+(DAY(A3)>=20);20) and extending back one month =DATE(YEAR(A3);MONTH(A3)+(DAY(A3)>=20)-1;21), based on the date in column A, I'd create auxilllary columns to hold those dates. Then you can use SUMPRODUCT to sum the amounts in (I suppose) column E ($E$3:$E$201) between those dates. You can include a SUMPRODUCT term to restrict the selection to lines with description "xxxxxx xxx" if that's required for your calculation.
[Tutorial] The SUMPRODUCT function

I do not understand from your attachment how one would know "the amount that was supposed to be paid".
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3941
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calculate payment over/shortage

Postby RusselB » Sun Dec 08, 2019 3:19 am

The amount to be paid would, initially, be the amount shown for minimum payment.
The time period would be from the 21st of the previous month up to and including the 20th of the current billing month.
ie: Sum of negative amounts in E from Jan 21 to Feb 20 would be allocated to the amount in column F with the date of Jan. 26
The date discrepancy is due to processing time by the company.

For my purposes, just seeing if the minimum payment was matched, exceeded or short on a month by month basis, with the start of the month being the same date as that in column A that also has an entry in column F
While that doesn't match the companies processing times exactly, I can work with the discrepancy.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5794
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests