If/Then Calculations

[mhemans]

I need to make a calculation that is, what I am calling an if/then statement.

This is what I am looking to do.

If "Field A" = 1 then "Field B" = 1, If "Field A" = 2 then "Field B" = 2, If "Field A" = 3 then "Field B" = 4, If "Field A" = 4 then "Field B" = 7, If "Field A" = 5 then "Field B" = 11, If "Field A" = 6 then "Field B" = 16, If "Field A" = 7 then "Field B" = 22

[transpower]

The Case statement would be more efficient. Like:

B =

Case ( A = 1 ; 1 ;

A = 2 ; 2 ;

A = 3 ; 4 ;

A = 4 ; 7 ;

A = 5 ; 11 ;

A = 6 ; 16 ;

A = 7 ; 22

)

[QuinTech]

Hi m, and welcome to FM Forums!

Make Field B a calculated field and set its options to make it unstored. Then use this calculation for Field B:

Choose ( Field A, 0 , 1 , 2 , 4 , 7 , 11 , 16 , 22 )

HTH,

Jerry

[QuinTech]

Oops, sorry for the echo. I got caught up in trying to work out a math-geek way of doing it.

J

[QuinTech]

My brain not being satisifed with this, i wrote a function to capture this in version 7, which allows recursion. I named the function fibonacciLike, because i know this is not the Fibonacci sequence, but it is something like it.

Case ( 

    n < 1 or n <> Int ( n ) ;

      0 ;

    n = 1 ; 

      1 ; 

    n - 1 + fibonacciLike ( n - 1 ) 

)

[smorr]

I need to make a calculation that is, what I am calling an if/then statement.

This is what I am looking to do.

If "Field A" = 1 then "Field B" = 1, If "Field A" = 2 then "Field B" = 2, If "Field A" = 3 then "Field B" = 4, If "Field A" = 4 then "Field B" = 7, If "Field A" = 5 then "Field B" = 11, If "Field A" = 6 then "Field B" = 16, If "Field A" = 7 then "Field B" = 22

The mathematical pattern here conforms to the pattern of triangular numbers

A triangular number is the sum of all the numbers upto and including the number

1:1

2:1+2=3

3:1+2+3=6

4:1+2+3+4=10

5:1+2+3+4+5=15

but it is a variant of it

where

1:1 -> tri(0) +1 or 0+1

2:2 -> tri(1) +1 or 1+1 = 2

3:4 -> tri(2) +1 or 1+2+1 = 4

4:7 -> tri(3) +1 or 1+2+3+1 = 7

5:11 -> tri(4) +1 or 1+2+3+4+1 = 11

6:16 -> tri(5) +1 or 1+2+3+4+5+1 = 16

7:22 -> tri(6) +1 or 1+2+3+4+5+6+1 = 22

So basically the pattern here is

Fieldb = tri(FieldA -1) +1

since triangular can be calcuated with a simple formula (thanks Gauss!) where tri(n) = n(n+1)/2

We are left with the mathematical formula: (adjusting for the fact we are looking at fieldA-1

FieldB = FieldA*(FieldA-1)/2 +1

eg when fieldA = 7, fieldB = 7(6)/2 +1 = 22

[QuinTech]

Brilliant!

[transpower]

This is all very nice, but the original poster did not include an ellipsis (...) after his If statement, so the sequence might not be the same after A = 7. That is, he gave a finite list, for which the above function would work, but if he added another value for A, then perhaps the above function would not work, in which situation he would have to resort to the Case function. But, hopefully, the function so work, as Simpler is Better.

[MoonShadow]

Wow. Thank you, Smorr. This will take me awhile to fully devour.

"That is, he gave a finite list..." Of course he gave a finite list, Transpower. What'd you expect him to do? Keep listing his sequence to infinity? Stephen might get upset at him for wasting Forum resources...

We need to assume (and rightfully so) that he provided the pattern. And the solution has been eloquently presented. In addition, we've been fed a sweet explanation of how it works and I really appreciate that.

I've learned from (and stolen) your CF also, Jerry!! BTW, using Choose() is NOT the 'math geek' way of doing it. I adore Choose() and it's much more efficient than Case().

[smorr]

Well, thanks to variety of postings, he now has several solutions to choose from. If he was presenting a finite list, the case or choose statements work very well. If his list was merely the first 7 terms from an infinite list which conforms to the triangular number pattern, he now has a formula that will work very well.

[QuinTech]

MoonShadow: I think smorr definitely out-math-geeked me (and i mean that in the best way possible, smorr!) I will point out, however, that i wrote my calculation on a beautiful sunny Saturday afternoon spent inside, so i think i should get some geek points for that.

Transpower: You are right, we are off topic quite a bit. From now on, we will stick to making terse, incomprehensible posts which will inevitably require a request for clarification.

[-Queue-]

[mhemans]

Thanks, The case calculation worked great.

[Ugo DI LUCA]

And I just "loved" Smorr approach...