- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Microsoft Excel Help with Formula (Updated with Picture of Spreadsheet)
Posted on 6/1/14 at 1:31 pm
Posted on 6/1/14 at 1:31 pm
I am trying to make a cell that will populate the date with the last time I input "NG" into column D where column A houses the date. I also want another cell to populate with the amount of days from the last aforementioned cell until present day. Does this make sense?
TIA
TIA
This post was edited on 6/1/14 at 8:57 pm
3
Posted on 6/1/14 at 1:41 pm to OleWarSkuleAlum
It took me a few times to understand what you were trying to say....but try this
=if(D="NG",A,"")
This will give you that date in A.
To do the difference in days, try this:
=today()-A
=if(D="NG",A,"")
This will give you that date in A.
To do the difference in days, try this:
=today()-A
Posted on 6/1/14 at 8:49 pm to OleWarSkuleAlum
Thank you for the extremely quick response to better clarify my situation I've included a picture...
Exactly what I want to do is: in another cell from D262:D999999 filter the most recent occurance of "NG". I want in that cell it to populate the date of that most recent occurance as shown in A.
So in this picture the most recent occurance of "NG" is 29-May-14, I want 29-May-14 to populate in the cell of my choosing based on D262:D999999.
Exactly what I want to do is: in another cell from D262:D999999 filter the most recent occurance of "NG". I want in that cell it to populate the date of that most recent occurance as shown in A.
So in this picture the most recent occurance of "NG" is 29-May-14, I want 29-May-14 to populate in the cell of my choosing based on D262:D999999.
Posted on 6/1/14 at 8:55 pm to Brettesaurus Rex
quote:
=if(D="NG",A,"")
This will give you that date in A.
Unfortunately this formula did not work.
Posted on 6/1/14 at 9:41 pm to OleWarSkuleAlum
=LOOKUP(NG,D262:D99999,A262:A9999)
=TODAY()-(=LOOKUP(NG,D262:D99999,A262:A9999))
This is off the top of my head, so you may have some formatting tweaking, but this should get you on the right track
=TODAY()-(=LOOKUP(NG,D262:D99999,A262:A9999))
This is off the top of my head, so you may have some formatting tweaking, but this should get you on the right track
Posted on 6/1/14 at 11:17 pm to Volvagia
I don't think that will work because the values in the lookup column D have to be sorted in ascending order.
EDIT:
Try this:
=MAX(if(D262:D999999="NG", A262:A999999))
EDIT:
Try this:
=MAX(if(D262:D999999="NG", A262:A999999))
This post was edited on 6/1/14 at 11:40 pm
Posted on 6/1/14 at 11:26 pm to LSU Tigerhead
Hmmmm...you might be right.
Now to play in Excel.
Now to play in Excel.
Posted on 6/2/14 at 9:45 am to OleWarSkuleAlum
Try this
=LOOKUP(2,1/(D262:D999999="NG"),A262:A999999)
=LOOKUP(2,1/(D262:D999999="NG"),A262:A999999)
This post was edited on 6/2/14 at 10:05 am
Posted on 6/2/14 at 11:36 am to OleWarSkuleAlum
Are you against using a simple Macro?
Would be fairly easy with a macro/do while loop.
Basically use Do While loop to progress down each row until empty (assume no empty rows in worksheet), check if D column is equal to "NG", then store date from column A, overwriting previous entry. If A is sorted ascending, it'll store the latest date.
I'm on my phone, so I can't help much with syntax.
Would be fairly easy with a macro/do while loop.
Basically use Do While loop to progress down each row until empty (assume no empty rows in worksheet), check if D column is equal to "NG", then store date from column A, overwriting previous entry. If A is sorted ascending, it'll store the latest date.
I'm on my phone, so I can't help much with syntax.
Posted on 6/2/14 at 9:02 pm to LSUtigerME
Stupid me the formula CubsFanBudMan gave me worked perfectly!! I just needed to format the cell to show the date instead of numbers.
The formula =LOOKUP(2,1/(D262:D999999="NG"),A262:A999999) is 100% correct
=TODAY()-(LOOKUP2,1/(D262:D999999="NG"),A262:A999999) shows the difference in days.
The formula =LOOKUP(2,1/(D262:D999999="NG"),A262:A999999) is 100% correct
=TODAY()-(LOOKUP2,1/(D262:D999999="NG"),A262:A999999) shows the difference in days.
This post was edited on 6/2/14 at 9:15 pm
Posted on 6/2/14 at 9:18 pm to LSUtigerME
As far as macros I just thoroughly confused myself going through the Microsoft Excel macro training online. What would be the benefit of a macro versus a formula?
Posted on 6/3/14 at 12:28 am to OleWarSkuleAlum
There's lots of different benefits.
Formulas are typically simpler and easier to share. Some workstations won't allow macros to be run, or at least give a security warning.
The advantage is you have a ton of options and much more robust programming. It's basically coding a "program" into your worksheet. For this situation, it seems like a formula is simple enough. But others often require a macro to execute the desired functionality.
In this example, you could write a code that starts at row #1, compares the column "D" if equal to "NG". If true, it'll store the date from column A. Then it'll progress to the next row, and repeat. This will happen until all rows are complete, and you're left with the last "NG" and respective date. You could also use this functionality to write an entire worksheet of only the "NG" data, or only "NG" data occurring in "May", etc. Although this specific example may been done with filters, macros could automate the process.
This loop functionality is very beneficial and can take advantage of a lot of the functionality of excel. There's a ton of uses for it and some more advanced programming, including input forms, form controls, etc. it requires a little bit of coding logic knowledge to write an efficient code, but it's very useful.
Formulas are typically simpler and easier to share. Some workstations won't allow macros to be run, or at least give a security warning.
The advantage is you have a ton of options and much more robust programming. It's basically coding a "program" into your worksheet. For this situation, it seems like a formula is simple enough. But others often require a macro to execute the desired functionality.
In this example, you could write a code that starts at row #1, compares the column "D" if equal to "NG". If true, it'll store the date from column A. Then it'll progress to the next row, and repeat. This will happen until all rows are complete, and you're left with the last "NG" and respective date. You could also use this functionality to write an entire worksheet of only the "NG" data, or only "NG" data occurring in "May", etc. Although this specific example may been done with filters, macros could automate the process.
This loop functionality is very beneficial and can take advantage of a lot of the functionality of excel. There's a ton of uses for it and some more advanced programming, including input forms, form controls, etc. it requires a little bit of coding logic knowledge to write an efficient code, but it's very useful.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News