## Need Help on If AND function

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

### Need Help on If AND function

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.

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

### Re: Need Help on If AND function

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

Hello Sir,

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

Thanks.
Attachments
Book1.xlsx

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

### Re: Need Help on If AND function

Hello Sir,

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

### Re: Need Help on If AND function

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