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.
[Solved] Checking payments
[Solved] Checking payments
- Attachments
-
- Sample.ods
- (21.37 KiB) Downloaded 103 times
Last edited by RusselB on Wed May 29, 2019 2:17 am, edited 1 time in total.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Checking payments
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.RusselB wrote:amounts summed are both negative and between the 26th of the previous month and the date in that row of column A
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.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Checking payments
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.