[Solved] Checking payments

Discuss the spreadsheet application

[Solved] Checking payments

Postby RusselB » Tue May 28, 2019 11:10 pm

In the attached file, I'd like to have an additional column that shows the difference between 10% of the amount in G and the total payments (negative amounts) made for the previous month, with the month changing on the 26th of each month.
Having a hard time wrapping my head around ensuring that the amounts summed are both negative and between the 26th of the previous month and the date in that row of column A.
The time period would actually be from the 27th of the previous month to the date in column A.
I hope this makes sense.
Attachments
Sample.ods
(21.37 KiB) Downloaded 14 times
Last edited by RusselB on Wed May 29, 2019 2:17 am, edited 1 time in total.
OpenOffice 4.1.7 and LibreOffice 6.0.6.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: 5485
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Checking payments

Postby MrProgrammer » Wed May 29, 2019 12:06 am

RusselB wrote:amounts summed are both negative and between the 26th of the previous month and the date in that row of column A
In H2, use =DATE(YEAR(A2);MONTH(A2)-1;27) to get the 27th of the previous month. Fill down. I will guess that column A is in ascending order and the formula only needs to examine previous rows. Leave I2 empty since there are no previous rows. In I3 calculate the sum with =SUMPRODUCT(C$2:C2;C$2:C2<0;A$2:A2>=H3). Fill down.

You may prefer to move the auxillary calculation in H to ZZ or some other unused column which you will not see. I wasn't sure from your description if you will also want condition A$2:A2<A3 as a fourth parameter.

SUMPRODUCT tutorial

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3836
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Checking payments

Postby RusselB » Wed May 29, 2019 2:17 am

Thanks... that's a lot of help, especially that Tutorial (not sure how I missed that one when I was searching before posting).
OpenOffice 4.1.7 and LibreOffice 6.0.6.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: 5485
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 32 guests