Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
The first row of the spreadsheet contains headers.
In Cells D2:D2500 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)),"") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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" wrote in message ... The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
"Bernie Deitrick" wrote: 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" wrote in message ... The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
=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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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. -- John C "John C" wrote: =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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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" wrote: 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. -- John C "John C" wrote: =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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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). -- John C "Gina" wrote: 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" wrote: 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. -- John C "John C" wrote: =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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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 "John C" wrote: 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). -- John C "Gina" wrote: 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" wrote: 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. -- John C "John C" wrote: =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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
That last question was my mistake- I'm sorry. Your formula is working
perfectly. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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. -- John C "Gina" wrote: 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 "John C" wrote: 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). -- John C "Gina" wrote: 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" wrote: 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. -- John C "John C" wrote: =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 "Gina" wrote: The first row of the spreadsheet contains headers. In Cells D2:D2500 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)),"") |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help me with this function? I am struggling..
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 "Gina" wrote in message ... That last question was my mistake- I'm sorry. Your formula is working perfectly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Struggling with IF formula...is it even the right one for me? | New Users to Excel | |||
Struggling IF value | Excel Discussion (Misc queries) | |||
Struggling for a solution | Excel Worksheet Functions | |||
Help, I'm Struggling! | New Users to Excel | |||
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. | Excel Worksheet Functions |