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

=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.

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.

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))

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))

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
---