combining IF and AND

[vba_php]

another simple one from me, folks. =( I have the table below. need assist with the last col. obviously this is financial data. last col needs to be cash that's avail to be taken out. tried using IF and AND, per title, but my formula workings is horrid. here's what i'm looking for:

• if purch avail < cash limit, cash avail = purch avail
• if cash limit = 0, cash avail = "n/a"
• if cash limit - cash bal < purch avail, cash avail = cash limit - cash bal
• if cash limit - cash bal > purch avail, cash avail = purch avail

I don't think i left anything out. you guys can probably tell what i'm doing at this point. didn't know how to put it into words. didn't want to write an essay. thanks.

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[AliGW]

The way to do this, Adam (because I know you don't just want to be handed a solution), is to create an IF statement for each of your conditions, then bring them together in one nested statement.

IF with AND gos something like this:

=IF(AND(x=y,y>z),...

[davsth]

Whilst not wanting to disagree with Ali, I think you have the option of ifs if you use 365. 2016 - 365 / 2007 is not so clear in a profile, otherwise nested ifs

if (purch avail < cash limit, purch avail,if(cash limit = 0, "n/a", etc etc))


or

ifs (purch avail < cash limit, purch avail,if(cash limit = 0, "n/a", etc etc)

[bebo021999]

May be:

=IF(G14=0,"n/a",IF(G14>H14,H14,MIN(G14-E14,H14)))

With G14=CASH LIM
H14=PUSH AVAIL
E14=CASH BAL

[vba_php]

thanks guys. Bebo, yours works fine, but does not for one line. did I leave out a conditional I wonder? I plugged the relative into all the cells, and only one
row was incorrect. this ask of mine is not working in the image below (item 1):

Please Login or Register  to view this content.

item 1's col should be 202, not 1958. all other rows work, but did not test all variations. see attached book for data i work with. and Pepe, I don't
need fast responses. whenever people can see my thread, that's fine by me.

[vba_php]

I didn't try the rest of your stuff guys, sorry. Ali, actually in this case I do want to be handed something. I do a lot of coding during the day and the last thing I want to do is learn something else I'm not good at. in this cases, math formulas. I'd rather just code the thing. =( but that's becoming extremely unpopular, for many reasons ya know? people are just not intelligent anymore. there's no thinking needed. just drag and drop. push the button and out pops the encrypted hash. but I figured I was doing the AND part wrong. I was trying to use is as an operator, not a nested function.

[AliGW]

Why should it be 202? Explain.

[AliGW]

people are just not intelligent anymore.

Please remember that you have been asked to avoid making comments like this by the admin team. It's completely irrelevant to your query.

but I figured I was doing the AND part wrong. I was trying to use is as an operator, not a nested function.

I showed you how it works as an operator, but it will have to become part of a nested function if you use it to solve this issue, as per your thread title:

combining IF and AND

Ali, actually in this case I do want to be handed something.

Can you see the irony here???

[davsth]

The question is why? cadsh adv bal is negative

=IF(L2=0,"N/A",IF(L2>M2,M2,MIN(J2+L2,M2))) perhaps


the cash adv balance is negative you also have changed the column headings to different terms to your original post with the logic

[vba_php]

Please remember that you have been asked to avoid making comments like this by the admin team. It's completely irrelevant to your query.

oh yes I realize I've been worn. And that's exactly why I don't have a future in this business. There's no place for somebody like me in this environment anymore :-(. Obviously.

but hopefully you guys and everybody else around here don't think that these comments are targeted to the question askers around here because they aren't it relates to the world completely.

But back on topic, no I don't see the irony But to answer your question about why 202 is the answer, the reasoning should be in the table headers aren't they?

[vba_php]

As you can see Ali the current amount of cash that is taken out on that record is 878. And the advanced limit is obviously 1080. That's why the answer is 202. you obviously can't take out more cash on a financial credit line than the cash limit right?

[vba_php]

The question is why? cadsh adv bal is negative

=IF(L2=0,"N/A",IF(L2>M2,M2,MIN(J2+L2,M2))) perhaps

the fact that some of the numbers are negative has nothing to do with anything. That's just the way I remember what money goes out and what money comes in.Negative and positive should make absolutely no difference here it's just a change in mathematical operators to get to the right answer and I figured you guys were smart enough around here to write it either way. but if you want me to change the negatives to positives I can certainly do that in order to get my answer in terms of the formulas I need from you guys if you're willing to give that thanks

[AliGW]

As you can see Ali the current amount of cash that is taken out on that record is 878. And the advanced limit is obviously 1080. That's why the answer is 202. you obviously can't take out more cash on a financial credit line than the cash limit right?

Obviously!

Thanks for clarifying. Seems you already have your answer. Please mark the thread as solved, if so.

[AliGW]

I figured you guys were smart enough around here to write it either way

We are all very smart - it goes without saying.

You should be able to use ABS(your_sum) to have it return a positive result regardless. I'll leave you to try it, because I think you should learn these things (just as you think other members here should learn). OK?