6 Replies - 371 Views - Last Post: 24 May 2013 - 09:44 AM Rate Topic: -----

#1 TurboST2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 11-November 11

Excel Search Function

Posted 24 May 2013 - 08:23 AM

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.
Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: Excel Search Function

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3624
  • View blog
  • Posts: 12,543
  • Joined: 12-December 12

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 row-numbers.

This post has been edited by andrewsw: 24 May 2013 - 08:40 AM

Was This Post Helpful? 2
  • +
  • -

#3 TurboST2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 11-November 11

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))
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3624
  • View blog
  • Posts: 12,543
  • Joined: 12-December 12

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"!
Was This Post Helpful? 2
  • +
  • -

#5 TurboST2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 11-November 11

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?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3624
  • View blog
  • Posts: 12,543
  • Joined: 12-December 12

Re: Excel Search Function

Posted 24 May 2013 - 09:21 AM

http://chandoo.org/w...ables-tutorial/
Was This Post Helpful? 2
  • +
  • -

#7 TurboST2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 11-November 11

Re: Excel Search Function

Posted 24 May 2013 - 09:44 AM

Thanks for your help!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1