First of all, you should check the "help" for the "or" function. It says: Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE and The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
that means that the stuff between the commas =or(test1, test2, test3) must END UP either TRUE or FALSE so, or(6,12,18,24... isn't going to work. because 6 isn't a true or false, 12 isn't true or false. I realize you WANT to test if A2 = 6, 12, 18... but to do that with OR, you'd have to use: =OR(A2=6,A2=12,A2=18, ....) Now, an easier approach would be to use something like the =MOD() function. =MOD() (short for "modulus") performs a division operation and returns the REMAINDER. So, you could use: =if(mod(A2,6)=0,(B2*C2)+B2,B2) although I'm not sure if the rest of your formula is correct... But this will at least get your first question answered... Paul ________________________________ From: Sastry <brb.sas...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Fri, February 19, 2010 12:44:25 PM Subject: $$Excel-Macros$$ formula for showing multiple of a no. Hi Group, I am writing a simple formula for a compounding interest. Let me quickly take you through the example. Monthly Investment - Rs.2000 Yearly Interest - 10% (compounded twice a year) i.e., 5% every 6th month Tenor - 1 year Month Investment Interest Cls Bal (Col A) (ColB) (Col C) (Col D) 1 2000 5% 2000 (no interest calc) 2 4000 5% 4000 (no interest calc) 3 6000 5% 6000 (no interest calc) 4 8000 5% 8000 (no interest calc) 5 10000 5% 10000 (no interest calc) 6 12000 5% 12600 (interest calculated @ 5%) My Question: I'm putting a formula assuming that they are in Columns A to D. I'm putting a formula in column D (Cls bal) as =IF(A2=OR(6,12,18,24,30,36,42,48,54,60,66,72,78,84,90,96,102,108,114,120), (B2*C2)+B2,B2) for some strange reason, this formula while using "OR" worksheet function is not working. Also, can anyone suggest if there is a shorter way of finding a substitute for "OR"? I want the interest to be calculated when the tenure is a multiple of 6 (since half yearly compounding). Your time and thoughts would be highly appreciated. Sastry -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
