Formula
 

I need a formula that calculates interest to receive if at least 4 of 5
shares are higher at determination date then subscription date. I tried with
=if(and/or, but it is not enough because those logical operators applies to
all shares.

Formula
 

I don't know what you algorithm for calculating interest is but to see if 4
or more of your share values have incresed try this:-

=IF(COUNT(IF(A1:A5B1:B5,A1:A5,FALSE))=4,"4 or more have increased","Less
than 4 have increased")

You start values are in A1 - A5 and the end values in b1 - B5. Its an array
so enter with Ctrl+Shift+Enter

Mike

"henriques" wrote:

I need a formula that calculates interest to receive if at least 4 of 5
shares are higher at determination date then subscription date. I tried with
=if(and/or, but it is not enough because those logical operators applies to
all shares.

Formula
 

Dear Mike
It doesn't work. The example in an excel table is like this

A1 1000 (Investment)
B1 2%
B2 8%
A3 60 B3 61
A4 50 B4 52
A5 75 B5 76
A6 100 B6 110
A7 8 B7 6
A3,A4,A5,A6.A7 are subscription date
B3,B4,B5,B6,B7 are determination date
In this example, because 4 of 5 shares are grater at determination date then
in subscription date the rate to apply will be 8%, but if you change, for
example A6 to 90, then we have 2 shares with value at determination date less
then at subscription date, and then the rate should be 2%.

"Mike H" wrote:

I don't know what you algorithm for calculating interest is but to see if 4
or more of your share values have incresed try this:-

=IF(COUNT(IF(A1:A5B1:B5,A1:A5,FALSE))=4,"4 or more have increased","Less
than 4 have increased")

You start values are in A1 - A5 and the end values in b1 - B5. Its an array
so enter with Ctrl+Shift+Enter

Mike

"henriques" wrote:

I need a formula that calculates interest to receive if at least 4 of 5
shares are higher at determination date then subscription date. I tried with
=if(and/or, but it is not enough because those logical operators applies to
all shares.

Formula
 

You can use the SUMPRODUCT formula to do this. Here is the formula if you
just want the appropriate interest rate...
IF(SUMPRODUCT(--($A$3:$A$7<$B$3:$B$7))=4,B1,B2)

The -- changes the values from True/ False to 1/0 and then sums them up.
Then it's checking if that sum is 4 or greater to give you the appropriate
interest rate.

Hope this helps.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"henriques" wrote:

Dear Mike
It doesn't work. The example in an excel table is like this

A1 1000 (Investment)
B1 2%
B2 8%
A3 60 B3 61
A4 50 B4 52
A5 75 B5 76
A6 100 B6 110
A7 8 B7 6
A3,A4,A5,A6.A7 are subscription date
B3,B4,B5,B6,B7 are determination date
In this example, because 4 of 5 shares are grater at determination date then
in subscription date the rate to apply will be 8%, but if you change, for
example A6 to 90, then we have 2 shares with value at determination date less
then at subscription date, and then the rate should be 2%.

Formula
 

Your formula is fine as well as Mike's formula. In the case of Mike I just
forget to enter the formula as an array (ctrl + shift + enter)

Thanks a lot for both
Henriques

"Michelle" wrote:

You can use the SUMPRODUCT formula to do this. Here is the formula if you
just want the appropriate interest rate...
IF(SUMPRODUCT(--($A$3:$A$7<$B$3:$B$7))=4,B1,B2)

The -- changes the values from True/ False to 1/0 and then sums them up.
Then it's checking if that sum is 4 or greater to give you the appropriate
interest rate.

Hope this helps.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"henriques" wrote:

Dear Mike
It doesn't work. The example in an excel table is like this

A1 1000 (Investment)
B1 2%
B2 8%
A3 60 B3 61
A4 50 B4 52
A5 75 B5 76
A6 100 B6 110
A7 8 B7 6
A3,A4,A5,A6.A7 are subscription date
B3,B4,B5,B6,B7 are determination date
In this example, because 4 of 5 shares are grater at determination date then
in subscription date the rate to apply will be 8%, but if you change, for
example A6 to 90, then we have 2 shares with value at determination date less
then at subscription date, and then the rate should be 2%.