Problems with IF Function

[n7mafia]

Hello,

I have been trying to make a simple layout to perform some basic calculations, but I encountered some troubles with the IF Function, I wonder if somebody can help me out? That's what I wanted to achieve.

I have let's say 3 different debts (1000, 2000, 10000) for each debt I know I got to pay a monthly rate of let's say (500, 1000, 1000)

I managed thru repetition fields to get a layout displaying the various months of the years and the resulting, diminishing total debt 1,2,3 something like:

Jan - 1000 / 2000 / 10000

Feb - 500 / 1000 / 9000

Mar - 0 / 0 / 8000

This part I managed. What I didn't manage is the IF function in the "Total to pay each month"

Results should be like:

Jan = (500+1000+1000=2500)

Feb = (500+1000+1000=2500)

Mar = (0+0+1000=1000)

The possibly incriminated IF Function reads like this:

If(Extend(Month 1b)>0;Extend(Rate Monthly 1);0) +

If(Extend(Month 2b)>0;Extend(Rate Monthly 2);0)

As per the picture, the calculation (Total Monthly) on the 6th, 7th, 8th, 9th, 10th, 11th lines should return 1000 as a result and from 12th line and on 0 and not 3000.

I am attaching the file for further references.

Debts.zip

[comment]

To sum two repeating fields (in a repeating calculation field), use =

RepeatingField 1 + RepeatingField 2

However, this is not a good method overall: what if there are three debts, not just two? What if they start in different months? You should not try to do everything in a single record.

[n7mafia]

I am not entirely sure I understood your suggestion, nor I am sure it applies to my problem? Would you mind to elaborate a bit?

Edited July 2, 2010 by Guest

[Raybaudi]

Ciao

Presumo che tu sia Italiano ( nomi dei mesi ).

Comment ti ha detto due cose:

1) Un campo calcolato multiplo, definito come somma di altri due campi multipli, vedrà riempire le proprie ripetizioni con le somme delle corrispondenti ripetizioni degli altri campi multipli.

2) Non necessariamente devi fare tutto in un singolo record ( in pratica NON usare campi multipli ma singoli campi in più record )

[comment]

I am not entirely sure I understood your suggestion

Which one? For the first one, see the attached.

Debts.zip

[comment]

in pratica NON usare campi multipli ma singoli campi in più record

I am not sure repeating fields are such a bad idea in this case.

[n7mafia]

I am afraid I did not manage to explain myself properly, I am sorry for the misunderstanding but what I meant is that the "Total Monthly" should display the sum of the rates I have to pay each month and NOT the total remaining from each debts.

In other words, on the file you attached the Total Monthly should display "3000" (2000+1000) for the first five lines and then "1000" for the remaining lines; since once a debt is paid I have no longer to pay a monthly rate/fee. So "Total Monthly" should calculate "Rate Monthly 1" + "Rate Monthly 2" and only if the debt is still going, otherwise it should return zero for the debt for which I finished paying and the monthly rate for the one going.

P.S. Si, sono italiano -)

[comment]

I meant is that the "Total Monthly" should display the sum of the rates I have to pay each month

Well, that's very simple. However, my earlier note still stands.

Debt2.zip

[Raybaudi]

Michael

could you explain the part:

[color:blue]i = n ; pmt - Mod ( -amt ; pmt ) ?

I saw that it omits works even when Mod( ) returns a zero value [ and so it is better than [color:blue]i = n ; Mod ( amt ; pmt ) and simpler than [color:blue]i = n ; If ( Mod ( amt ; pmt ) ; Mod ( amt ; pmt ) ; pmt ) ], but I do not understand how it works.

[comment]

If:

Mod ( a ; b ) ≠ 0

then:

Mod ( a ; b ) + Mod ( -a ; b ) = b

[Raybaudi]

Finally I understood...

Mod ( 10 ; 7 ) = 3

Mod ( -10 ; 7 ) = 4

IOW: 7 - 4 = 3

[comment]

Or, if you like:

Mod ( a ; b ) is the distance from Floor;

Mod ( -a ; b ) is the distance to Ceiling.

[n7mafia]

Worked like a charm, thank you.