Spreadsheet help?

General discussions not related to the Vibe, Matrix, or any other vehicle. (follow posting rules)
Post Reply
User avatar
zionzr2
Posts: 3174
Joined: Tue Jul 27, 2004 4:59 am
Location: Austin, TX

Spreadsheet help?

Post by zionzr2 » Sat Jan 29, 2011 8:35 am

Ok I need help from some of the Spreadsheet gurus if there are any here.I have a data set the contains 2 columns the first column lists the States and territories the second column is the number of occurrences a particular event occurs in each. What I need help with is I need a function that will return the state from the first column based on it being the max value in the second column and if there is a tie for max value that each state that is a max value is returned. I have a function that works for the first part but it will only return the first max value it reaches in the list and ignores any others with the same value.For example My current formula =INDIRECT("A"&MATCH(MAX(C1:C60),C1:C60,0)) if 5 is the max number of occurrences, and Arizona, Colorado, and Texas each have 5 then my current formula only returns Arizona. I would like it to return all three, ideally separated by commas.Any help is greatly appreciated. Also the spreadsheet is created using OpenOffice.Thank You

User avatar
zionzr2
Posts: 3174
Joined: Tue Jul 27, 2004 4:59 am
Location: Austin, TX

Post by zionzr2 » Sun Jan 30, 2011 8:32 pm

Bump

User avatar
Whelan
Posts: 3980
Joined: Tue Jan 23, 2007 10:30 pm

Re: (zionzr2)

Post by Whelan » Sun Jan 30, 2011 11:44 pm

have you tried V-Lookup
2012 GLI Autobahn 6MT

User avatar
Wolfman213
Posts: 1377
Joined: Fri Sep 05, 2008 12:58 am
Location: Anderson, IN

Post by Wolfman213 » Mon Jan 31, 2011 12:55 am

If you are wanting it to pull and of the matching hgih states in one cell, you probably will have to get into the VB side of things (if OpenOffice does that) bc as far as I know, that will be way more complex than a formula can pull since it will probably end up in a circular reference error. If you want them to be listed say in A1:A? then it can be done. I found a decent formula the other day in regards to this and I'll try looking it up again. If involved the "IFERROR" function which I've actually been utilizing a lot more recently and seeing it's potential.The vlookup will only return one answer as well. The MAX function helps in determining the largest number for you, but I've not been able to think or find anything that well return all the results in one cell using formulas alone and not actual written VB code.
April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage

User avatar
Whelan
Posts: 3980
Joined: Tue Jan 23, 2007 10:30 pm

Re: (Wolfman213)

Post by Whelan » Mon Jan 31, 2011 2:01 am

VLookup returned one answer per the cell you are correllating to. If you utilize the $ sign before each letter/number of the cells when first inputting the formula and then drag down, you can find results based on the total of the column you are picking from in relation to each individual cell in the other column.sound confusing, it is. VLOOKUP is not an easy formula to get the hang out without trial and error, but when it works, it is awesome!You can also try making a Pivot Table.
2012 GLI Autobahn 6MT

User avatar
Wolfman213
Posts: 1377
Joined: Fri Sep 05, 2008 12:58 am
Location: Anderson, IN

Post by Wolfman213 » Mon Jan 31, 2011 2:36 am

But if he has 50 states that he's going through, then he'll also have to have 50 cells to look at rather than have all the information in either one cell or listed together based on how many actually meet the criteria of the highest.Not familiar with Pivot Tables at all lol.Here is code out on the web that will at least give you only the ones meeting your criteria. Granted, it will not place them in one cell, but just fill the corresponding cells.=IF(ISERROR(INDEX($A$1:$B$50,SMALL(IF($A$1:$A$50=$A$51,ROW($A$1:$A$50)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$50=$A$51,ROW($A$1:$A$50)),ROW(1:1)),2))In order for this to work, here's somethings you will need to do. Place the numbers in column A and the states in column B. In A51, us the formula =MAX($A$1:$A$50) when you place the larger formula in a cell, you MUST hit (CTRL+SHIFT+ENTER) so it will place it as an array, otherwise it will not work. This should get you started at least.
April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage

User avatar
joatmon
Posts: 9412
Joined: Fri Mar 21, 2003 5:19 am
Location: SMC MD

Re: Spreadsheet help? (zionzr2)

Post by joatmon » Mon Jan 31, 2011 3:49 am

My pc is snafu so posting from memory from the droid (so i might not have it exact)You could pick another column and set formula to be something likeif(C1=MAX($C$1:$C$60),A1&", ","") so that cell will contain the name of the state followed by a comma and a space if that state's value is the max or nothing if notDo that for every row in D or do it in D1 and copy it into D2:D60 (changing the C1 and A1 appropriately)Then somewhere else, say D61, set a cell to be =CONCATENATE(D1,D2,D3,..........D60) That should give you a list of all the high states separated by a comma and a space. Finally, where you want the end result to show up set that cell to be =SUBSTITUE(D61,", ","",COUNTIF(D1:D60,"*, "))This counts the number of items in column D that end in comma space and substitues the last one will nothing, pruning the trailing comma space.from cell D61

User avatar
Wolfman213
Posts: 1377
Joined: Fri Sep 05, 2008 12:58 am
Location: Anderson, IN

Post by Wolfman213 » Mon Jan 31, 2011 4:43 am

(removed)!!!!!!!!! I can't believe I didn't think of that!!!! And the fact you spouted all that off from memory without a computer in front of you, BRAVO Joatman!!!!!!!!!!!!
April 2010 Co-MOTM * Custom LED Gauge Cluster swap * LED dome, HVAC, and shifter * Retrofit projector w/ blue halo * Kenwood KDC-HD942U * Herculined cargo area * Debadged *Wolfman's Garage

User avatar
zionzr2
Posts: 3174
Joined: Tue Jul 27, 2004 4:59 am
Location: Austin, TX

Post by zionzr2 » Mon Jan 31, 2011 5:50 am

Thanks for all the input, but I decided to go a slightly different route, but have a similar tie issue.I no longer want the results to a single cell (seems to difficult). I want to create a 3 column by 5 row data set that will show the top 5 states. so if there is any ties this list would account for it. If there is a tie between lets say CA and TX then Alphabetical will break the tie.Column 1 is text identifying rank (i.e. 1st, 2nd,...)Column 2 is the State meeting the rankcolumn 3 is the number of occurrences that meet Largest from 1st to 5th largest.

User avatar
zionzr2
Posts: 3174
Joined: Tue Jul 27, 2004 4:59 am
Location: Austin, TX

Post by zionzr2 » Mon Jan 31, 2011 8:45 am

I think I got it now!!Heres how I did itfirst I used a helper column that ranked the states using filled down the column.=RANK(C2,$C$2:$C$60,0)+COUNTIF($C$2:C2,C2)-1 Then in the table I wanted the top 5 I used=INDIRECT("A"&MATCH(SMALL($E$1:$E$60,1),$E$1:$E$60,0))Changing the green number up one till 5 for each rank.

Post Reply
cron