CompeteWithPete Posted August 9, 2016 Share Posted August 9, 2016 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?? Quote Link to comment Share on other sites More sharing options...
Guest rasteri Posted August 9, 2016 Share Posted August 9, 2016 All I know is that if you select the word "blue" and click on the excel logo you get a pinball game. Quote Link to comment Share on other sites More sharing options...
Deft Posted August 9, 2016 Share Posted August 9, 2016 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") Quote Link to comment Share on other sites More sharing options...
CompeteWithPete Posted August 9, 2016 Author Share Posted August 9, 2016 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 Quote Link to comment Share on other sites More sharing options...
Mutis Mayfield Posted August 9, 2016 Share Posted August 9, 2016 At school the teacher said:Make it bits first.Assemble working bits. ITOH maybe starting in reverse rejecting false values before run the formula... It makes sense? Boolean wasn't my best skills xD Quote Link to comment Share on other sites More sharing options...
Deft Posted August 10, 2016 Share Posted August 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
CompeteWithPete Posted August 10, 2016 Author Share Posted August 10, 2016 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! 1 Quote Link to comment Share on other sites More sharing options...
Deft Posted August 10, 2016 Share Posted August 10, 2016 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. 1 Quote Link to comment Share on other sites More sharing options...
CompeteWithPete Posted August 10, 2016 Author Share Posted August 10, 2016 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(matchit was like man discovering fire Quote Link to comment Share on other sites More sharing options...
Deft Posted August 10, 2016 Share Posted August 10, 2016 Index/Match is truly next level.Pivot Tables were a major turning point in my love affair with Excel. Quote Link to comment Share on other sites More sharing options...
Guest Symatic Posted August 11, 2016 Share Posted August 11, 2016 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? Quote Link to comment Share on other sites More sharing options...
Deft Posted August 11, 2016 Share Posted August 11, 2016 Probably, I can definitely do that in Excel. Conditional formatting. I don't use the Googles. Quote Link to comment Share on other sites More sharing options...
joeClockwork Posted August 11, 2016 Share Posted August 11, 2016 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 Quote Link to comment Share on other sites More sharing options...
CompeteWithPete Posted August 11, 2016 Author Share Posted August 11, 2016 thats easy, this is straight from google Select all the data.Go to Home > Conditional Formatting > New rule.Click on Format only cells that containChange Cell Value option to BlanksSet up formatting you want by clicking on Formatting button.Click ok and you are done! Quote Link to comment Share on other sites More sharing options...
Guest Symatic Posted August 11, 2016 Share Posted August 11, 2016 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 Quote Link to comment Share on other sites More sharing options...
joeClockwork Posted August 11, 2016 Share Posted August 11, 2016 Do you want to apply conditional formatting that references another cell? There's a 'Use a formula to determine which cells to format' option in Excel. Google Sheets should have something similar. Unless I'm completely missing the point! Quote Link to comment Share on other sites More sharing options...
Guest Symatic Posted August 11, 2016 Share Posted August 11, 2016 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$$ 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.