Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
i want to try this again. I am trying to determine a winner for each county.
In b5:e5 I have my canidates. In column A6:a139 i have my counties, B6:E6 i have the votes for each canidate. in G6 i have my total votes, and in H6 i need to input the winner by name using a formula. I tried =index(b5;e5,match(max(b6:e6),B6:e6)) that gave me the person with the least votes and i need the highest votes. Please tell me what am i doing wrong. I am on windows vista home premium, excel 2007, service pack 1 thanks Shone |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
=index(b5;e5,match(max(b6:e6),B6:e6))
You must set MATCH to look for an exact match (match type = 0): =INDEX(B5:E5,MATCH(MAX(B6:E6),B6:E6,0)) since B6:E6 is not necessarily in ascending order Do note that in the event of ties in the maximum scores, the expression will return only the leftmost candidate name If above helped in any way, click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Shone33" wrote: i want to try this again. I am trying to determine a winner for each county. In b5:e5 I have my canidates. In column A6:a139 i have my counties, B6:E6 i have the votes for each canidate. in G6 i have my total votes, and in H6 i need to input the winner by name using a formula. I tried =index(b5;e5,match(max(b6:e6),B6:e6)) that gave me the person with the least votes and i need the highest votes. Please tell me what am i doing wrong. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
how can I copy this formula to my other cells with out it changing the first
part of my formula. I tried the next cell and it change the formula to =index(b6:e6,match(max(b7:e7),b7:e7,0)) "Max" wrote: =index(b5;e5,match(max(b6:e6),B6:e6)) You must set MATCH to look for an exact match (match type = 0): =INDEX(B5:E5,MATCH(MAX(B6:E6),B6:E6,0)) since B6:E6 is not necessarily in ascending order Do note that in the event of ties in the maximum scores, the expression will return only the leftmost candidate name If above helped in any way, click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Shone33" wrote: i want to try this again. I am trying to determine a winner for each county. In b5:e5 I have my canidates. In column A6:a139 i have my counties, B6:E6 i have the votes for each canidate. in G6 i have my total votes, and in H6 i need to input the winner by name using a formula. I tried =index(b5;e5,match(max(b6:e6),B6:e6)) that gave me the person with the least votes and i need the highest votes. Please tell me what am i doing wrong. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
You fix it to always point to the names in row 5 using $ signs, like this:
In H6: =INDEX(B$5:E$5,MATCH(MAX(B6:E6),B6:E6,0)) Then when you copy H6 down, it'll propagate correctly, viz, you'd get in H7, H8, etc ...: =INDEX(B$5:E$5,MATCH(MAX(B7:E7),B7:E7,0)) =INDEX(B$5:E$5,MATCH(MAX(B8:E8),B8:E8,0)) etc P/s: When you post your queries in the newsgroups, just copy your formula directly from inside the formula bar, then paste it into your post. Reverse the process when the responders give you the formula; copy direct from the responder's reply, then paste it into your formula bar. Do not re-type. Its a waste of manual effort, and you're likely to introduce errors, typos as well. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Shone33" wrote: how can I copy this formula to my other cells with out it changing the first part of my formula. I tried the next cell and it change the formula to =index(b6:e6,match(max(b7:e7),b7:e7,0)) |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
thanks can you answer this?
If parker has 136 votes and allen will receive 66% of parker votes and then webb will receive 24% of parker votes. and then parker will keep the remaining votes. what formula do i use. I tried =SUM(D7*0.66+B7). this formula also added webb and allen current votes. was this formula correct? "Max" wrote: You fix it to always point to the names in row 5 using $ signs, like this: In H6: =INDEX(B$5:E$5,MATCH(MAX(B6:E6),B6:E6,0)) Then when you copy H6 down, it'll propagate correctly, viz, you'd get in H7, H8, etc ...: =INDEX(B$5:E$5,MATCH(MAX(B7:E7),B7:E7,0)) =INDEX(B$5:E$5,MATCH(MAX(B8:E8),B8:E8,0)) etc P/s: When you post your queries in the newsgroups, just copy your formula directly from inside the formula bar, then paste it into your post. Reverse the process when the responders give you the formula; copy direct from the responder's reply, then paste it into your formula bar. Do not re-type. Its a waste of manual effort, and you're likely to introduce errors, typos as well. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Shone33" wrote: how can I copy this formula to my other cells with out it changing the first part of my formula. I tried the next cell and it change the formula to =index(b6:e6,match(max(b7:e7),b7:e7,0)) |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
excel
I've posted some thoughts in that thread.
It's not clear. Take it up further in a new thread. P/s: Always keep it to one specific query per post. Make it attractive for responders to respond. And try to put in better, more meaningful subject lines which summarizes the key issue that you want to ask. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|