Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-22-2011, 11:21 AM
knownunknown knownunknown is offline Analysis of states and income levels Windows 7 64bit Analysis of states and income levels Office 2010 32bit
Novice
Analysis of states and income levels
 
Join Date: Jan 2011
Location: London
Posts: 4
knownunknown is on a distinguished road
Default Analysis of states and income levels

I have a column of states and a column of income, and I need to find the median income for each state. A sample of my two columns of data would be something like this:



CA - $80,000
CA - $23,000
CA - $30,000
AZ - $50,000
AZ - $60,000
AZ - $70,000
FL - $55,000
FL - $65,000
FL - $95,000

Is there a way to do this dynamically? I can sort by state and select the fields manually, but I'd really like to know if there's a way to do it using a function as I'll probably need to repeat this process as new data comes in.

So far this is what I've come up with, but it doesn't seem to work:

=MEDIAN(IF(A:A="CA",B:B))

Edit: Ok, nevermind, solved. The above function is correct, just needed to hit CTRL SHIFT ENTER.

Last edited by knownunknown; 01-22-2011 at 01:07 PM.
Reply With Quote
  #2  
Old 01-22-2011, 06:35 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Analysis of states and income levels Windows 7 32bit Analysis of states and income levels Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Just some follow up info since it wasn't concluded on this thread -

If you are using Excel 2007 or later then you can use entire column references in array formulas, SUMPRODUCT() etc... If you are using versions prior to Excel 2007 then you can't.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stock levels RobertH Excel 5 01-24-2011 01:02 PM
Analysis of states and income levels Numbering hierarchy in different levels of headlines jaybo Word 4 01-18-2011 12:42 AM
Analysis of Amount based on following criterion. aligahk06 Excel 1 04-24-2010 10:34 AM
Income/Budget Calendar? Guinea Excel 4 03-11-2010 09:11 PM
Word Heading Levels spock0149 Word 2 06-04-2009 06:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft