Jump to content


Photo

Summarizing data by category in Excel

pivot table MAX VLOOKUP

  • Please log in to reply
1 reply to this topic

#1 Adam

Adam

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 23 September 2014 - 11:38 AM

Hello,

I need a formula for type of highest grant  to come on Cell B7 (not the amount but I need a type of grant. e.g. research and policy.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 23 September 2014 - 06:37 PM

Hi Adam,

 

I'm not completely sure what you're asking here, but I'm guessing you have data that looks something like this:

 

grants.png

 

...and you want to create a formula that will extract the grants with the highest amounts for the Research and Policy categories.

 

If all you want to do is get the maximum amount for each grant type, the best solution would be to use a pivot table. 

 

grants2.png

 

Creating a pivot table like this one should be easy with the skills taught in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

However, it is not quite so easy to extract the data that accompanies the highest grants (for example, the Grant ID).  It can be done, but it requires some rather complex formulas.

 

An easier solution is to break your data into separate tables for each grant type, like this:

 

grants3.png

 

You can now easily extract the highest grant amount from each table by simply using the MAX function, and can then get any associated data using the VLOOKUP function.

 

For more about the MAX function, see the video lesson: Essential Skills Lesson 2-12 Create your own formulas.

For more about the VLOOKUP function, see the video lesson: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

I have attached a copy of my example workbook, showing both the pivot table and formula-based approaches.

 

Attached File  Grants.xlsx   16.1KB   215 downloads

grants4.png

 

As I said above, there is a more complicated way to do this without splitting up your data.  If you'd like me to explain this approach, or if you need any more assistance with this, please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users