Jump to content

excel if, and, then, or function


CompeteWithPete

Recommended Posts

so im bankruptcy dept and need to see when to initiate home reposession depending on delinquency.

 

the cells i need to condition are

-d2 (7,13)

-f2 (contractual date)

- o2 (post date)

 

i just need true or false values.

 

the condition is if a 7 is today()-contractual date > 30 days then true, if not false.

 

additionally i want to add if a 13 is today()-post date >60 then true, if not false.

 

so far this formula takes but only returns false values even if the formula is true:

 

=if(d2="7",and(today()-f2>30,or(d2="13",and(today()-o2>60,0))))

 

im pretty its and, or, or then.. fukkit i duno, any suggestions??

Link to comment
Share on other sites

If you are using Excel 2016 the new IFS function will probably do the job e.g.

=IFS(D2=7,IF(TODAY()-F2>30,"TRUE","FALSE"),D2=13,IF(TODAY()-O2>60,"TRUE","FALSE"))

 

If using an older version of Excel you could try the following, though the logic might be screwy - it's too late for me to concentrate.

=IF(OR(AND(D2=7,TODAY()-F2>30),AND(D2=13,TODAY()-O2>60)),"TRUE","FALSE")

Link to comment
Share on other sites

we're still working on 2010 i think at work, maybe 13.

 

i just tested your argument and i get a msg saying "try using an insert function if you're using an =. try an apostrophe... yada yada ". i know this formula is possible and its been driving me crazy all day. i tried THEN instead of OR but i get false values.

 

its what i love/hate about excel.

 

thx for trying tho i appreciate. once i get it ill post my the correct formula

Link to comment
Share on other sites

You get an error with the second formula when entering it exactly as shown? Weird - I just copy/pasted it back from this thread to Excel and it is a valid formula for me (not saying it definitely does what you want).

 

we're still working on 2010 i think at work, maybe 13.

i just tested your argument and i get a msg saying "try using an insert function if you're using an =. try an apostrophe... yada yada ". i know this formula is possible and its been driving me crazy all day. i tried THEN instead of OR but i get false values.

its what i love/hate about excel.

thx for trying tho i appreciate. once i get it ill post my the correct formula

Link to comment
Share on other sites

so i just got to work and tried it again and it worked!!!! ur a genius!!

 

i had to type it out a few times and it kept highlighting the 2nd AND statement.

 

i went back and forth with ur post and my screen and i finally saw it....

 

i had to add a comma before the 2nd AND argument and it worked!

 

i still have more learning to do. i never would of thought to start with 3 statements (if, or,and) then and.

 

im going to im u direct from now on 🙌🏻 thx!

  • Like 1
Link to comment
Share on other sites

ur a genius!!

Finally the recognition I deserve. Everyone else on this board with world DJ titles are feeling pretty stupid right now.

Next time, just copy and paste! Don't type. Typing is for chumps.

Also, triple check it performs properly under all circumstances. These tricky formulas or nested logic are always a source of bugs in my programming experience.

  • Like 1
Link to comment
Share on other sites

u truly are next level.

 

if a dj cant speak formula, then u aint sayin nothin.

 

i like typin out formulas so i remember them. i have post its all over my monitor and emails to myself for reference. i emailed this one lol.

 

i was recently was stuck one day thinking about how a reverse vlookup would help me.

 

after some searchin I discovered =index(match

it was like man discovering fire

Link to comment
Share on other sites

Guest Symatic

I got a nerdy question...

 

In google sheets, can i make a whole row turn red if the cells in a particular column are empty? So when i fill those cells the row stops being red?

Link to comment
Share on other sites

This is hilarious. I've been having an Excel problem all morning and couldn't find anything online. I then find the solution on a scratch DJ forum!

 

Cheers Deft/Pete!


I got a nerdy question...

In google sheets, can i make a whole row turn red if the cells in a particular column are empty? So when i fill those cells the row stops being red?

 

https://support.google.com/docs/answer/78413?co=GENIE.Platform%3DDesktop&hl=en

Link to comment
Share on other sites

Guest Symatic

Nice one pete but the other cells in that row contain other info, some of them have rules to ad shit together across the row. The fact they alredy got text in em makes the red go away basically.

I beed the redness to depend on the cell in one colum only.... If that makes sense

Link to comment
Share on other sites

Guest Symatic

i've done it now, got help from a pro :)

 

we used Conditional Formatting.

 

i had to apply it to a range (eg. A2:A1001) and then the custom formula was =U$:U$="". It focuses on column U because theres a $ after the letter U. and I put the : there to sat from U to U, cos i wanted to ONLY look at column U. if i wanted a range of stuff it would be U$:z$ or something.

then theres another = to tel the formula to do something. the thing i want it to pay attention to is that it is blank. you put the action in quotes and cos i wanted it to act when the cell is blank you just ut the quotes and nothing else. so you get "".

 

so =U$:U$="" means "look at any cell between column U to column U that has nothing in it" and then the action (which is listed below the formula part of the google sheets Conditional Formatting window) is just to colour stuff in.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...