Forum Discussion
Help with IF & AND with different outcomes for a Line of Credit Advances & Payments
Hello Excel Formula Experts!
This is my first time here so I hope I have posted all the correct info &file
I’m need of help with this formula:
=ROUNDDOWN(IF(AND(H29>0,I26>1000,I26<=H29,H30<$E$24),(I26),0),IF(AND(H29>0,I26>1000,I26>=H29,H30<$E$24),H29,0)-3)
This is a picture of part of the spreadsheet (picture didn't copy here)
I have also attached a copy of the work book with the formula I’m having trouble with (I removed the password on it but it keeps asking for it anyway – pw is: lucy123 if you need it to open )
The formula I’m working with is in Row 27 (highlighted in yellow) I believe the formula I have written conveys what I’m trying to do but it doesn’t work in all cases, so I guess I’m missing something or I’m using the wrong formula.
I’m trying to calculate this in Row 27 “LOC – Paydowns”
If H29(LOC Used) is greater than 0
And – I26(Adjusted Ending Cash) is greater than 1000
And – I26 is less than H29(Line of Credit used)
And – H30(Line of Credit Available) is less than E24(Max Line of Credit)
Then = I26 (use all of cash Balance) rounded down to the nearest 1000 (paydown partial balance)
Otherwise:
If H29(LOC Used) is greater than 0
And – I26(Adjusted Ending Cash) is greater than 1000
And – I26 is greater than H29(Line of Credit used)
And – H30(Line of Credit Available) is less than E24(Max Line of Credit)
Then = H29(Line of Credit Used) rounded down to nearest 1000 –Pay down the full balance
3 Replies
Laurie-
I think this may be what you want in cell I27:
=IF(AND(H29>0,I26>1000,I26<H29,H30<$E$24),ROUNDDOWN(I26,0),IF(AND(H29>0,I26>1000,I26>=H29,H30<$E$24),ROUNDDOWN(H29,0)-3))
Can you give an example of where the formula doesn't work as expected? i.e. Add an extra row that shows the value that you expect to be returned. This way it will be easier to diagnose the issue.
Also what is the -3 intended to do?
Hi Matt, thank you for your reply. I did get this solved with this formula ( I had too many brackets and was telling it to be 0 in both cases, over thinking it!) . The -3 at the end is to rounddown to the nearest 1000.
=ROUNDDOWN(IF(AND(C29>0,D26>1000,D26<=C29,C30<$E$24),D26,IF(AND(C29>0,D26>1000,D26>=C29,C30<$E$24),C29,0)),0)Glad you were able to resolve the issue! Nothing like that great feeling when the light bulb comes on and everything is as it should be in the world!!
