I'm working with a HUGE set of data. Column A is years from 1990  2010. There are multiple instances of each year, for example, there are roughly 1500 1990's in column A that has data in other columns that corresponds to that specific 1990.
I need a formula that will give me the cell reference of the first 1990 in column A and a cell reference for the last 1990 in column A so that I can perform calculations based on that range. I would need to be able to alter this formula so that I can then get cell references for the first and last occurrence of each year so that I can make calculations based on the ranges of each year.
Here is a sample screenshot of my data for reference.
Excel Search Function
Page 1 of 16 Replies  398 Views  Last Post: 24 May 2013  09:44 AM
Replies To: Excel Search Function
#2
Re: Excel Search Function
Posted 24 May 2013  08:39 AM
If the table remains in sorted order (by year) you could use
=MATCH(1990,A3:A2000,0)
to return the first row number and
=MATCH(1990,A3:A2000,1)
to return the last.
But what's wrong with a Pivot Table? Even using COUNTIF, SUMIF (or database functions) wouldn't require you to manually determine these rownumbers.
=MATCH(1990,A3:A2000,0)
to return the first row number and
=MATCH(1990,A3:A2000,1)
to return the last.
But what's wrong with a Pivot Table? Even using COUNTIF, SUMIF (or database functions) wouldn't require you to manually determine these rownumbers.
This post has been edited by andrewsw: 24 May 2013  08:40 AM
#3
Re: Excel Search Function
Posted 24 May 2013  08:51 AM
I'm not sure how I would use COUNTIF or SUMIF with the type of answers I need.
For instance, one of my questions is to determine the average CO2 emissions for each year due to coal. So would I do something like this?
=IF(1990,AVERAGEIF(D:D,"Coal",E:E))
For instance, one of my questions is to determine the average CO2 emissions for each year due to coal. So would I do something like this?
=IF(1990,AVERAGEIF(D:D,"Coal",E:E))
#4
Re: Excel Search Function
Posted 24 May 2013  09:08 AM
If you are using Excel 2007+ then
=AVERAGEIFS(E3:E2000,A3:A2000,1990,D3:D2000,"Coal")
but this is definitely screaming "pivot table"!
=AVERAGEIFS(E3:E2000,A3:A2000,1990,D3:D2000,"Coal")
but this is definitely screaming "pivot table"!
#5
Re: Excel Search Function
Posted 24 May 2013  09:11 AM
Perfect! That =AVERAGEIFS formula is exactly what I needed. Never new about it. For future reference though, what is a pivot table?
Page 1 of 1
