Excel Formula Help

[shahzad_afzal]

Hi; I am using a lookup formula to get the last date of any reference number and then add one sec to it...
=IF(D10="New Addition",E10,LOOKUP(3,(1/($C$4:$C$54=C10)+(1/$G$4:$G$54<>"")),$G$4:$G$54)+1/86400)
Its is doing good... What I need is to restrict this function to cells in column before cell formula is running... Example: formula has to look at C10 and get max date; it will only use data from C4:C9... Data below it, don't want to use...

[#2]

@shahzad_afzal 

You clearly know about absolute and relative cell references, which would be part of the answer. But what do you do if you're in Cell C345? Is there always only a difference of 5 rows between the start and finish row for that formula you're asking about.

In other words, what we need from you is a bit more information on the larger context here...how is the table being built?

By the way, another  (and I think clearer) way to add a second to the value of another date&time cell is to use +TIME(0,0,1)

[#3]

@mathetes I am attaching a test case for you to review...

When you sort data with the date which I have to, every thing will get disturbed...

I just want the highest date till the selected row and not after that to be used to get dates; else every time; highest date is displayed from the column...

[#4]

@shahzad_afzal 

I am not following you at all...that is, I don't understand what you're trying to do with the sample spreadsheet; your words in this last post don't fully make sense either.

When you sort data with the date which I have to, every thing will get disturbed...

• Sort?
• Sort data with the date which I have to? ("have to" meaning "must do"?)
• everything will get disturbed? (supposed to get disturbed; will in the course of "sorting")

I just want the highest date till the selected row and not after that to be used to get dates; else every time; highest date is displayed from the column...

• Which column(s) are you referring to? There are five columns in your example; three of them are dates AND times.
• What is supposed to happen with column E? How do column D and F affect that.
  • Please answer that question in words....I know that's what your formula is trying to do, and it's what your question is about. But spell it out more completely.

[#5]

@mathetes sorry for the confusions...

Column E will have two type of dates; one if new addition and date will come from Column D... When activity ends; next time will have one second addition... Column E will get date & time from Column D for all activities other than new addition...

Now come to Column B & Row 10; ABC2000078304... If i will copy paste formula; i will get highest date from column F which is F17 Cell... I want it to get date with reference to cell F10 only...

Hope this makes sense...

[#6]

@shahzad_afzal 

Sorry. Still not making sense.

Walk me though what's happening in each column in rows 9 through 19.

• The two entries "New Addition" and "Termination" make sense.
• What does "Locked" mean? what does "Active" mean?
• And how does each event affect the various time stamps?

[#7]

@mathetes A customer joined on new addition... After some time; customer wants to lock his internet link or terminate when not needed... Scenario 1; customer ID ABC2000078304 (Cell B9); came on network... Stayed active from 12th Mar till 16th Mar and went to lock...

The moment i run lookup; E10 will get date from F17 which is the highest date in the column... It has to be 16th Mar not 25th Mar...

Just copy paste formula from E5 onwards till E28, you will see that column E will get populated with highest date record from column F...

I want ABC2000078304 related record, whatever row number it is; will get date from the previous record only, rather than max date from column F... 

[#8]

@shahzad_afzal 

I know it's clear in your mind. It's not in mine. A big part of the confusion in my mind is (I think) caused by Column E apparently being the final or more important part of the record, yet appearing between D and F...so it's that relationship and your reference to rows that appear to FOLLOW rather than precede (like why does E10 get a date from F17?)...

Let me ask again, and I'll not confuse it by asking subordinate questions:

Row by row, what's happening? Walk me though what's happening in each column, especially E, in each of the rows, 9 through 19.

[#9]

@mathetes Row 9; a new addition of customer number ABC2000078304... Manual entry of Date in Column D... In column E, it will get date from D as its a new addition...

After 4 days; customer decided to lock it link temporary for any reason; a manual entry in column F...

As the status changed to Locked; added a new row to show customer went to lock and will stay in lock till the time a manual entry of activity end is added in column F against it...

After less than 1 hour; customer decided to be active again... Added a manual entry and activity of lock ended and customer moved to active state in row 11... same type of lock and active state will go on till the time customer decided in row 18 to terminate the link on 25th Mar... Active status ends with a manual entry in F17 and shows being terminated in E18...

1 Sec is a system delay to process the request...

If I run the lookup formula; like I shared in my first message; excel will get 25th mar date in E10 as the maximum date of any customer whereas I need the date of last instance in E10 which is 16th Mar 5:59:56...

Hope this is clear now...

[#10]

@shahzad_afzal 

You wrote: Hope this is clear now...

But it's not quite.

In particular. when you say If I run the lookup formula ... excel will get 25th mar date in E10 as the maximum date of any customer whereas I need the date of last instance in E10 which is 16th Mar 5:59:56...

But as I look at your sheet, the value in E10 is March 16, 2020 5:59:57; the value you cite as what you need is in D9 (the cell that feeds E10)     So which is it that you need? And, more importantly for writing the formula, why is it that cell? What distinguishes it? You say it's "the last instance", but "last instance of what"? precisely.

Let me re-raise a question I asked you early yesterday:  if I go back to your second message yesterday you said something that I questioned at the time. I need to ask again. You wrote: 

When you sort data with the date which I have to, every thing will get disturbed...

I just want the highest date till the selected row and not after that to be used to get dates; else every time; highest date is displayed from the column...

It is now striking me that maybe a big part of the confusion here, in what you're saying AND in the procedure itself that you're following, is that you routinely sort this table, from appearances the San ID column (column B) is the primary sort, and Activity Start is the secondary sort. Is that the case? Do you enter new event data at the bottom of the table and then sort? That could indeed have the effect you describe: "everything will get disturbed."

If that's the case, the solution would be, first, to separate the  procedure by which you collect the event data from the procedure by which you display it. Said another way, to distinguish between input and output.

The reason: if you are indeed sorting this table routinely, that will disturb the relationships between rows, as things get rearranged.

So first please clarify that aspect of how you add rows (new customers, new events) to this table in the first place. And answer the question of whether or not it gets sorted so as to distinguish between customers.

[#11]

@shahzad_afzal     I think I understand what is trying to be done here.  I agree with  @mathetes that it might be a good practice to set up the sheet as a database input sheet and then use pivot table to display the data in the desired format.  But to @shahzad_afzal  question I believe you are looking for the formula in the original post to be corrected to work and that would be to use partial absolute and relative references similar to what is being used in the sample sheet you provided.  

so in the sample sheet row 10 you have:

=IFERROR(IF(C10="New Addition",D10,LOOKUP(B10,$B$4:B9,$F$4:F9)+1/86400),"")

notice how it has absolute reference for $B$4 but relative reference for B9

I believe you want to switch to a formula that looks like the one you posted so you need to have a similar mix of absolute and relative references (modified to work on row 10 of sample sheet)

=IF(C10="New Addition",D10,LOOKUP(3,(1/(B$4:B9=B10)+(1/F$4:F9<>"")),F$4:F9)+1/86400)

that said you might also consider another variation that I find easier to read:

=IF(C10="New Addition",D10,MAXIFS($F$3:$F9,$B$3:$B9,$B10)+TIME(,,1))

I am also not clear about what your problem actually is since the formula you already have on the sheet appears to be working but hopefully these variations might help.  If not please explain this problem with sorting and it getting mixed up.

EDIT:  I just had a thought if you are moving things around and such and having issues with finding the "correct" max previous end time then just add the whole table to the MAXIFS and add another condition that the END TIME must be less than the END TIME from this row but had to add another IF condition for the "Termination" to just use the max time because there is no END TIME in that row:

=IFS(C4="New Addition",D4,C4="Termination",MAXIFS(F:F,B:B,B4)+TIME(,,1),TRUE,MAXIFS(F:F,B:B,B4,F:F,"<"&F4)+TIME(,,1))

[#12]

@mtarler Dear Team; Thanks for jumping in to help me out... I am attaching version 2, with absolute values for the whole data...

Let me make it more open...

Is this possible to get date from last activity of relative SAN??? Whatever is the last activity...

Forget about lookup and any formula I already shared... I don't want to get max date from the column against relative SAN ID... I just want next activity to find previous activity, get its date & time and add 1 sec to it and done...

There will be 1000s of SANs with multiple activities daily... You can suggest sorting type too, either SAN based or activity date...

Regards

[#13]

@shahzad_afzal 

You still haven't answered the question about whether or not it is your practice to sort this table routinely. That's an important question. Neither @mtarler nor I was suggesting that you should do so; we're asking IF you do.

And it appears that you do, just from the evidence that SANID ABC2000078423 begins activity (is a New Addition) with a date that precedes the termination date of ABC2000078304. So you're either sorting OR you are adding rows by insertion. Either way, you're not handling this as a neat and simple Excel Table for input.

The kind of formula you are looking for is an easy matter.

But it's important to know how this table is being maintained from the point of view of data entry. And then it might well make a lot of sense to treat it solely as a data entry table (Input), and create a separate tab that extracts the data and sorts it to form a  useful report (Output).

I'm going to create a mockup of what I'm describing for you and will post it shortly.

[#14]

@mathetes Hi; Data entry will be manual like adding new addition date; then adding activity end date, which will help to get next activity start date and so on till termination... I would love to sort this by activity date as every month need to generate a month activity report and revenue report out of it... I can add another sheet and can use VLOOKUP to get new addition date into the formula... Rest all data will be here...

[#15]

@shahzad_afzal 

You wrote: Data entry will be manual like adding new addition date; then adding activity end date, which will help to get next activity start date and so on till termination...

Which doesn't yet make it clear. Of course you add new data by entering an ID, an activity description, and a date. The question was aimed at how the table is maintained, which wasn't so much what data are entered--that's pretty obvious--but whether or not you enter each transaction at the bottom of the table, regardless of which SAN ID it is, or do you insert a row in the section for the given SAN ID? 

Let me take a few minutes to create a mock-up for how transactional databases are generally laid out, with an example of what could then be created as an output report.

[#16]

@shahzad_afzal  I looked at your new sample sheet and the 1st table with Absolute and Relative reference seems to do what you want.  Is there a problem with that?  I still don't know what was not working with the original lookup (using abs and relative references) in the original sample sheet.  I have attached a sheet with multiple variations for the START TIME using each of the formulas.  all of them except the fully absolute reference seem to work.  Let me know what isn't working if needed, but I think the last one I gave is the most 'robust' solution.  I attached the file and included an conditional formatting that highlights any START TIME that is > END TIME to highlight problems.  That highlighted the first row because the New Addition Date is actually after the END TIME.

[#17]

@shahzad_afzal 

Here's a totally different approach. It uses most of your data but approaches it by separating INPUT from OUTPUT.

The input table uses most of your raw data, but simplified to only three columns and they've been entered in date and time sequence:

• Start date and time
• SAN ID
• Transaction (event)

Then I use two formulas only to create the output.

• The first one is =SORT(Table1,2) which sorts the input table on the basis of the second column, which is SAN ID   It "spills" down to populate however many rows there are in the basic input table. That could be refined if need be, to limit it in various ways, depending on your needs. SORT is one of the new Dynamic Array functions, available only in the newest release of Excel. If this doesn't work for you, that's why.
• The second formula is =IF(C5=C4,B5-TIME(0,0,1),"") and that actually derives the end time from the start time of the NEXT activity for the same SAN ID. In other words, I've reversed your logic. AND I only create the end time at the report stage of things. It's assumed that when you start a new stage of the contract with a given SAN ID, that it means the last stage is now superceded.

I would encourage you to add additional events, new SANIDs, at the bottom of the input sheet. You'll see that the output sheet will automatically keep up with it.

[#18]

@mathetes Dear; added more data (see attached) and if sorted by SAN ID, things are perfect...

New data addition will always be in the last row; what is the possible option to get the date from the previous instance without sorting by SAN ID?

[#19]

@shahzad_afzal 

I need to ask what you thought of the version I created.

But also, whether you use that or not, you really haven't ever clarified what the whole process is that you are trying to create. It's clear that you're capturing data on various events, but toward what purpose? Does a report sorted by SAN ID have a place? Would it be better to just extract the full history of any given SAN ID? Given that you've said you have thousands of records, it just isn't clear what you really want. The formula is easy once you define things clearly.

To your question: New data addition will always be in the last row; what is the possible option to get the date from the previous instance without sorting by SAN ID? I need to ask again, "previous instance" of what?? Last instance for the given SAN ID? Say that. Last instance over all? Say that. Date and time of start  or end?

[#20]

@mathetes Hi; Yes I am referring to last instance for the given SAN ID...

Sorting by SAN ID is the last resort if unable to get last instance for the given SAN ID; else need is sort by activity date...