Please help me with this function? I am struggling..

[Gina]

The first row of the spreadsheet contains headers.

In Cells D22500 my spreadsheet gives a list of dates specific events occur.

In Cells G2:G2500 my spreadsheet lists the district in which these events
occur.

In Cells C2:C2500 I need to count the # of days since the last event that
occurred within this district.



I am trying to get this formula to work, and can't seem to do it (but thank
you JohnC, I think you've given me an excellent start- if I was better at
this, I'd have probably figured it out and wouldn't need to ask this
question).



=IF(COUNTIF($D$1:$D2,D3)<>0,D3-MAX(IF(($G$2:$G$2500=G3)*($D$2:$D$2500<D3),$D$2:$D$2500)),"")

 

[Bernie Deitrick]

Gina,

Select your data table, then choose Data / Pivot Table and Pivot Chart Report... and then click
"Finish"

Drag the button with the distirct names into the Row Field Area, and the button with the "Date"
column heading into the data area - right click the data button and choose "Field Options" and set
that to Max. Format the field for date, and you will have a table that list the latest date for
each District. If you want differences (the number of days), type

=TODAY() - B4

into a cell next to the pivot table, format for number with no decimal places, then drag down to
match your table.

HTH,
Bernie
MS Excel MVP

 

[Gina]

Gina,

Select your data table, then choose Data / Pivot Table and Pivot Chart Report... and then click
"Finish"

Drag the button with the distirct names into the Row Field Area, and the button with the "Date"
column heading into the data area - right click the data button and choose "Field Options" and set
that to Max. Format the field for date, and you will have a table that list the latest date for
each District. If you want differences (the number of days), type

=TODAY() - B4

into a cell next to the pivot table, format for number with no decimal places, then drag down to
match your table.

HTH,
Bernie
MS Excel MVP

Hi Bernie,
I did try that, and it does give the last date for each district, but that's
not exactly what I need.

What'll happen is that several departments are entering data into this
spreadsheet. It may be entered at times out of chronological order.

I want in Column C a function that will look at the current record-- take
the current date and district, and then look at the entire dataset of 2500
rows and tell me out of those, how many days it's been since the previous
event. I need this calculation for each record.

Then once I get this done, I will in another spreadsheet, go back and query
the max amount of days that any one district has gone between events, and
return that max value into a cell. (we give out awards if certain levels are
met- 30/60/90 days within a calendar year). The data goes back several
years, and will extend several years into the future.

 

[John C]

=IF(COUNTIF($G$1:$G1,G2)<>0,D2-MAX(IF(($G$2:$G$2500=G2)*($D$2:$D$2500<D2),$D$2:$D$2500)),"")

 

[John C]

Remember, this formula, entered in C2, is an array** formula, and when you
first enter it, press CTRL+SHIFT+Enter to commit it. Then copy down as needed.

 

[Gina]

If you ever do consulting, email me. I get offered stuff way over my head
from time to time-- aka-- the level of Excel you normally occupy.

Gina

 

[John C]

lol, thanks for the feedback. But trust me, everything I know is self-taught,
and there are multiple posters on here who know way more than me. But again,
thanks for the feedback. (I had noticed you didn't change all the cell
references as needed , and since I still had the 'scrap work', I modified
and fixed).

 

[Gina]

I noticed it works great- but has one tiny bug.

If the Central District has two claims on the same day- the claim on the
lower row returns the value 34790. I could make a search/replace macro for
34790 to 0, unless you know a quickfix?

Gina

 

[Gina]

That last question was my mistake- I'm sorry. Your formula is working
perfectly.

 

[John C]

The one caveat that I had I mentioned on your original posting, and forgot to
remention here, is the first time any district is entered, that should be the
first alarm for that district. For example, say in row 8 is the first time
you have Central District entered. The date in column D should be the
earliest date of all alarms for that district. I have changed it so that it
checks if that is the earliest.
=IF(SMALL(IF(($G$2:$G$2500=G2),$D$2:$D$2500),1)=D2,"",D2-MAX(IF(($G$2:$G$2500=G2)*($D$2:$D$2500<D2),$D$2:$D$2500)))
Still an array formula.

 

[Bernie Deitrick]

Gina,

I'm not sure why you use the leading COUNTIF fucntion - it actually throws things off if the dates
are out of order. This seems better - array entered:

=IF(MAX(IF(($G$2:$G$2500=G2)*($D$2:$D$2500<D2),$D$2:$D$2500))>0,D2-MAX(IF(($G$2:$G$2500=G2)*($D$2:$D$2500<D2),$D$2:$D$2500)),"")

This also takes care of the error produced when the date in D is the earliest date for that
district.

HTH,
Bernie
MS Excel MVP