Need Help on If AND function

shreyas981
Posts: 3
Joined: Tue Feb 28, 2017 9:53 am

Need Help on If AND function

Postby shreyas981 » Tue Mar 07, 2017 9:18 am

Hello Sir,

I have query with respect to IF AND function. i have developed 4 line formula using only "IF" function & it runs properly. However, when i saw online lecture on IF AND function, i guess, i can reduce the length of the formula by using IF AND function. However, i am not able to use it and getting proper results.

Following are the logical test to be used -
Logical Test 1 - Selected Date should be more than 0 date
Logical Test 2 - "Quarterly" or "Monthly"
Logical Test 3 - if quarterly, then repayment should be quarterly then Month=3, Month(particular Cell)=6,Month(particular Cell)=9,Month(particular Cell)=12,
Logical Test 4 - if monthly then repayment should be monthly.

i have used following formula with "IF" & "Hlookup" function -
=IF(M17>$O$7,IF($O$8="Quarterly",(IF(MONTH(M17)=3,$N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE)),IF(MONTH(M17)=6,$N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE)),IF(MONTH(M17)=9,$N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE)),IF(MONTH(M17)=12,$N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE)),$N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE))/4*0))))/HLOOKUP(A17,$N$11:$X$14,4,FALSE)),($N$8*(HLOOKUP(A17,$N$11:$X$13,2,FALSE)/HLOOKUP(A17,$N$11:$X$14,4,FALSE)))),0)

PLs guide me how to use "IF AND" function.

Thanks in advance.

edupristine
Finance Junkie
Posts: 791
Joined: Wed Apr 09, 2014 6:28 am

Re: Need Help on If AND function

Postby edupristine » Tue Mar 07, 2017 12:50 pm

Hi Shreyas

Can you please attach the respective excel.

shreyas981
Posts: 3
Joined: Tue Feb 28, 2017 9:53 am

Re: Need Help on If AND function

Postby shreyas981 » Tue Mar 07, 2017 1:29 pm

Hello Sir,

PFA attached file. Please refer "O17" cell for formula.

Thanks.
Attachments
Book1.xlsx
(273.15 KiB) Downloaded 59 times

shreyas981
Posts: 3
Joined: Tue Feb 28, 2017 9:53 am

Re: Need Help on If AND function

Postby shreyas981 » Wed Mar 15, 2017 11:34 am

Hello Sir,

Please reply...

edupristine
Finance Junkie
Posts: 791
Joined: Wed Apr 09, 2014 6:28 am

Re: Need Help on If AND function

Postby edupristine » Mon Apr 24, 2017 7:41 am

Hi Shreyas,

PFB
=IF(M17>$O$7,IF($O$8="Quarterly",IF(OR(MONTH(M17)=3,MONTH(M17)=6,MONTH(M17)=9,MONTH(M17)=12),$N$8*HLOOKUP(A17,$N$11:$X$13,2,FALSE),$N$8*HLOOKUP(A17,$N$11:$X$13,2,FALSE)/4*0)/HLOOKUP(A17,$N$11:$X$14,4,FALSE),$N$8*HLOOKUP(A17,$N$11:$X$13,2,FALSE)/HLOOKUP(A17,$N$11:$X$14,4,FALSE)),0)

One things, you shall check is that if it is not a month out of 3,6,9,12.. then it will be zero (always) according to your formula.. which seems incorrect.. I have bold the part of the formula... you are multiplying the full formula with 0... let us know...

Regards


Return to “Open Source Financial Modeling”



Disclaimer

Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to abuse@edupristine.com and we will rectify it.