 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Formula
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. 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:A5>B1: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 > 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. 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%. > 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:- [quoted text clipped - 11 lines] > > =if(and/or, but it is not enough because those logical operators applies to > > all shares. 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.  SignatureCheers, Michelle "Anyone who says he can see through women is missing a lot." Groucho Marx > Dear Mike > It doesn't work. The example in an excel table is like this [quoted text clipped - 13 lines] > 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%. 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 > You can use the SUMPRODUCT formula to do this. Here is the formula if you > just want the appropriate interest rate... [quoted text clipped - 23 lines] > > 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%. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.