LinkBack URL
About LinkBacks
hello all,
I have created a table with index match function.Everything is working great but when in the table two values are equal it gives n/a is there anyway to get rid of this please.thank you.
hello all,
I have created a table with index match function.Everything is working great but when in the table two values are equal it gives n/a is there anyway to get rid of this please.thank you.
Last edited by mdshotgun; 06-29-2010 at 11:43 AM.
It shouldn't. Can you upload an excel sheet example?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Can you give an example of the formula that produces #N/A error when 2 values match?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
it wont !but where isscothland
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
i am trying to make a prediction hockey game based on daddylonglegs wc.I have upload a sample.please have alook.
martin i don't understand what you mean."where is scothland"
in b4
=INDEX($A$50:$C$61,MATCH(A7,$A$50:$A$61,0),2)
a7 contains 4
there is no match in $A$50:$A$61 you need to unique rank
try in a50
=RANK(C50,$C$50:$C$61,0)+COUNTIF($C$50:C50,C50)-1
fyi my details say location London,England
yours say Scothland
Last edited by martindwilson; 06-28-2010 at 05:30 PM.
martin,
that is workin great.
would you mind explaining how that works.
put the 2 parts in seperate cells
=RANK(C50,$C$50:$C$61,0)
and
=COUNTIF($C$50:C50,C50)-1
rank assigns same rank to = amounts so if there were 3 20's in c50:c52and they were ranked 1
you'd have 1,1,1 so the count of 20 in $c$50:c50 =1 so (rank 1 ) +(the count of 1 - 1) =1
the next count of 20 in c$5$0:c52 is 2 so rank 1 +(the count of 2 - 1) =2
and lastly
the count of 20 c$5$0:c53 is 3 so rank 1 +(the count of 3 - 1) =3
i could have explained that better but hey ho i'm not really a teacher lol
ah but a better (much better) explanation here
http://www.cpearson.com/excel/Rank.aspx
i think this is where i learnt it in the first place!
Last edited by martindwilson; 06-28-2010 at 07:45 PM.
martindwilson thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks