You are not logged in.

#1 2023-11-16 00:51:04

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

[Solved] LO-Calc help

Basically read the 446 page PDF manual and have pinhead step in.  cool

I have LO-Calc and have NO IDEA how to use it.

This is Argentina so don't ask me to explain why BUT we get a monthly water for our house and 4 of our neighbours.

The water company separates the amount into what each house (Unit) owes BUT we are responsible for paying the whole thing.  BTW we are Unit 03 so not needed in the calculations because what the neighbours do not pay we MUST pay or they shut the water off (for all of us).
Yea yea I know a messed up system.  Welcome to Argentina!

So I have a sample spreadsheet, text only, for Nov and Dec and leading into Jan:
aysa-2.jpg
I have got a headache from googling this and now have a mental block.

4E-4D = what should be in 9C
5E-5D = what should be in 10C
etc etc

If Unit 01 pays 2000 (9E) in Dec (9E-8E = 15C)
etc etc.

I cannot add the subtotals because of the 120% inflation rate and the subtotals are going up monthly.

Here's my problem:
NO idea how to set up the calculations in the spreadsheet

Anyone have a clue as to how I can do this.
·  I'll be adding a month at a time obviously and it needs to continue the calculations:
· · · as I put in this months "Paid Amount" it calculates next months "In Favour"
· · · as I add the monthly "Sub Total" it will calculate "Total To Pay"

Any help appreciated.
Thanks

Last edited by Sector11 (2023-11-16 20:54:38)


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#2 2023-11-16 03:56:10

Döbbie03
Resident Metalhead
From: New Zealand
Registered: 2015-09-29
Posts: 3,853

Re: [Solved] LO-Calc help

1+1=2

After that I am lost.


"All we are is dust in the wind, dude"
                                       - Theodore "Ted" Logan
"Led Zeppelin didn't write tunes that everybody liked, they left that to the Bee Gees."
                                       - Wayne Campbell

Offline

#3 2023-11-16 06:05:15

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

I probably didn't understand it at all, but:
4E-4D = what should be in 9C
=IMSUB(E4;D4)

it will calculate "Total To Pay"
=SUMA(D4:D7)


Complete the pattern, solve the puzzle, turn the key.

Offline

#4 2023-11-16 14:14:43

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

I probably didn't understand it at all, but:
4E-4D = what should be in 9C
=IMSUB(E4;D4)

Yea something not right:
Results in: Err:504


Pinhead wrote:

it will calculate "Total To Pay"
=SUMA(D4:D7)

Results in: #NAME?


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#5 2023-11-16 16:10:29

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

Is the cell format - number?


Complete the pattern, solve the puzzle, turn the key.

Offline

#6 2023-11-16 16:18:49

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help


Complete the pattern, solve the puzzle, turn the key.

Offline

#7 2023-11-16 17:24:16

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

Is the cell format - number?

Hope so, all I did was type the number in.

Did some looking ... yea number:general

I've learned that

=b2-c2

in

b3

works BUT if someone pays enough for a few months I get a negative number that really messes this up.

what is difference between

=IMSUB(B42,C42)

and

=B42-C42)

Must have been a typo on my part earlier.

The pdf I grabbed earlier today says:

IMSUB(Complex number 1,Complex number 2)
Returns the difference of two complex numbers

I'm getting it as a negative difference if C42>B42 same as

=B42-C42

  I need just the difference not negative

 16 Nov 23 @ 14:40:05 ~
   $ c -1072.94-798.98
-1871.92
 
 16 Nov 23 @ 14:40:41 ~
   $ c 1072.94-798.98
273.96
 
 16 Nov 23 @ 14:40:53 ~
   $ 
                             Total
        Sub Total   a favor  a Pagar    Pagado
Unit 01	959.84      45.32    914.52     1000
Unit 02	798.98      1871.92  -1072.94   798.98
Unit 04	1108.56     3.18     1105.38    1110

AHA!!!!  IMABS  ... trying
NOPE!  That didn't work.


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#8 2023-11-16 17:41:28

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

so the absolute difference

=ABS(B42-C42)


Complete the pattern, solve the puzzle, turn the key.

Offline

#9 2023-11-16 17:44:06

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

so the absolute difference

=ABS(B42-C42)

YES!!!!   Thank you!


Now to check other stuff.


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#10 2023-11-16 20:49:21

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

@ Pinhead

Thank you kindly.  So simple but for someone that had no clue a puzzle.

Again Thanks.


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#11 2023-11-16 20:55:22

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Dobbie03 wrote:

1+1=2

After that I am lost.

1+1=10  smile


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#12 2023-11-16 21:15:27

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

Sector11 wrote:

@ Pinhead

Thank you kindly.  So simple but for someone that had no clue a puzzle.

Again Thanks.

You're welcome smile


Complete the pattern, solve the puzzle, turn the key.

Offline

#13 2023-11-16 22:11:56

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

You're welcome smile

And I'm back again because my wife says I'm crazy.

Can anyone look at this an tell me if I going nuts.
I do not want to cheat our neighbours but I also do not want to pay their bill

Again
- Unit 03 is us we pay the full thing, then the neighbours pay their portion
- B: The amount on the monthly bill that each unit owes
- C any $ left over from the previous month (=E-D of previous month)
- D (=B-C) of same month This is where I used =ABS(B33-C233), or I'd have a - number

No one has give anything towards Dec yet except the one unit (02) that like to pay well in advance.
I filled in the "Sub Total" for Jan 24 but we already know that's low. Just to show how C & D react

Links: https://ibb.co/ZScYQV5  & https://ibb.co/X5Bj296


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#14 2023-11-17 08:06:45

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

OK, then the negative number is correct.
https://ibb.co/NWM3k5K/

According to that, there were mistakes in the past months.
E.g. in November, Unit 02 had a large leftover from the past. So she didn't have to pay anything. Likewise in September and August...

Last edited by Pinhead (2023-11-17 08:14:52)


Complete the pattern, solve the puzzle, turn the key.

Offline

#15 2023-11-17 16:22:50

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

OK, then the negative number is correct.
https://ibb.co/NWM3k5K/

According to that, there were mistakes in the past months.
E.g. in November, Unit 02 had a large leftover from the past. So she didn't have to pay anything. Likewise in September and August...

That can't be right.

Someone gets a bill for:  1897.11
has in their favor:       1560.53
they have to pay:          336.58

BUT they give me 4000.00 pesos so they do not need to pay for a couple of months.

Next month they have

 17 Nov 23 @ 12:35:33 ~
   $ c 4000-336.58
3663.42 «« to their favour

NOT -3663.42, which would be the the result IF I didn't use ABS.
Your example with -red numbers has Pay me "around double" what the bill says when they already paid extra because of that - number in "a Favor" when it's really a positive number needed there..

How can they possible have a negative value in the "In Favour" column?

Since I control the "manual input" of columns B and E
· B what the bill says
· E how much each person gives us

· if C is greater that B in a given month, I use that in column E for that month which reduces the "in Favor" of the following month.  For that to work I need the ABS you showed me.

Does that make sense?
AYSA-2.jpg

Look at Unit 02 as you go down Col E (E-D=C)

Also look at DEC one unit paid for DEC his In favour balance for Jan 24 is 47.31


Next up: Teach myself to make a "template" for coming years with no numbers but text and formulas in place.
Chapter 4 Using Styles and Templates in Calc


And it hit me - what is someone says: Can I pay you next month?

Need a "test" sheet.


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

#16 2023-11-17 16:35:04

Pinhead
Member
Registered: 2022-12-05
Posts: 98

Re: [Solved] LO-Calc help

So I don't understand it at all anymore...


Complete the pattern, solve the puzzle, turn the key.

Offline

#17 2023-11-17 17:44:09

Sector11
Mod Squid Tpyo Knig
From: Upstairs
Registered: 2015-08-20
Posts: 8,008

Re: [Solved] LO-Calc help

Pinhead wrote:

So I don't understand it at all anymore...

Then you can imagine me

I use to do it in a terminal

$ c 959.84-45.80
914.04

Bill says: 959.84
45.80 in favour from last month
914.04 to pay this month

They'd give 1000 for the month

   $ c 1000-914.04
85.96

= 85.96 credit for next month. Hmmm: Crédito better than a favor
And put it in a text file.

--- MAY -------------- A Y S A - 2 0 2 3 ----
 VENCIMIENTO: 03/05/2023                    |
 #  |        |  Menos  |  Total   | Pagado  |
 de | Sub-   |    a    |    a     |  este   |
Unid| Total  |  favor  |  Pagar   |  mes    |
---- -------- --------- ---------  --------- 
*01   959.84 -   45,32 =   914,52 | 1000.00 |

--- JUN -------------- A Y S A - 2 0 2 3 ----
 VENCIMIENTO: 09/06/2023                    |
 #  |        |  Menos  |  Total   | Pagado  |
 de | Sub-   |    a    |    a     |  este   |
Unid| Total  |  favor  |  Pagar   |  mes    |
---- -------- --------- ---------- --------- 
*01   959.84 -   85,48 =   874,36 |  880.00 |

but with all 4 units.

Now that's a pain!!!

So I though spreadsheet.

Hmmmm I have a "tax auditor" friend I might ask her for help.

However with your help I am leagues ahead of where I would have been.
So the Thank You still applies.


Debian 12 Beardog, SoxDog and still a Conky 1.9er

Offline

Board footer

Powered by FluxBB