• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HIghlighting [SOLVED]

madocar

madocar

Member
Hello guys


Would u mind to help me with my issue. I attached excel file.

Columns B and C consist of combinations of my bet. 1 means that the hometeam wins, X means the game is tied ... According to match result and correct combination (green background) I want to highlight that particular cell in column N. So in this case in first match Barcelona won and second match Chealsea tied against Everton. So I want to highlight the combination 1 - X in column N by blue color automatically right after putting down those two results (yellow background). Is there any chance doing it using correct formulas?


http://www71.zippyshare.com/v/99890359/file.html


Many thanks indeed
 
Hi Madocar..


Interesting query..


Still few things to clrify..

* Are you always looks in to N-th Column..


In a cell.. write below formula.. Please confirm the formula by Ctrl + Shift + Enter.. not just enter..


Code: 
=INDEX(MMULT($G$2:$G$4,TRANSPOSE(H2:H4))/2-3,MATCH($B$13,$F$2:$F$4,0),MATCH(C13,$F$2:$F$4,0))


Waiting for your confirmation.. if any way to determine in which Column need to look up.. Except N


https://dl.dropboxusercontent.com/u/78831150/Excel/chandoo%20bet.xlsx


Regards,

Deb
 
Hi ,


Can you please clarify the following ?


1. You merely want to highlight the cell in column N when the data entered is 1 - X ?


2. You mention that you want this to happen when you enter data in the cells coloured yellow , B12 and C12 ; but these are just two cells , whereas you have a lot of results entered in columns B and C , in the range B2:C10. The results in column N are all derived through formulae in all the other columns J through M. And these results in column N are not dependent on B12 and C12. They are dependent on the table in F2:H4.


3. Will the table in F2:H4 be the same always , or can it change ? If it remains the same , then does it not follow that when ever a result in column B is 1 , and the result in column C is X , then the corresponding result in column N will always be 0.6425


4. The CF formula can colour the cell in column N blue simply on the basis that the value is 0.6425 If not , then the CF formula can be =AND(cell in column B = 1 , cell in column C = "X").


Narayan
 
Hi guys


Thank you very much

I'm uploading the same file with notes. I hope after reading them you will understand me


http://www61.zippyshare.com/v/77160234/file.html


Thanks again
 
Hi Madocar,


Did you missed my previous post.. :)


Can you please download the above file in my previous post..


Regards,

Deb
 
Hi, madocar!


First you should modify your A13:B13 formulas to these:

=SI(IZQUIERDA(A12;HALLAR(":";A12)-1)=DERECHA(A12;LARGO(A12)-HALLAR(":";A12));"X";SI(VALOR(IZQUIERDA(A12;HALLAR(":";A12)-1))>VALOR(DERECHA(A12;LARGO(A12)-HALLAR(":";A12)));"1";"2")) -----> in english: =IF(LEFT(A12,SEARCH(":",A12)-1)=RIGHT(A12,LEN(A12)-SEARCH(":",A12)),"X",IF(VALUE(LEFT(A12,SEARCH(":",A12)-1))>VALUE(RIGHT(A12,LEN(A12)-SEARCH(":",A12))),"1","2"))

so if any team marks more than 10 goals they'll work fine too (or if you use it for another sport).


Second I didn't understand how to obtain the row for the right combination (marked as this ">->->->->->->->->->->->->->->->->->->->->->->->->->"?). Would you elaborate?


Regards!
 
Hi Madocar ,


Select your data range in column M , and enter the following formula for CF :


=ROW($M2)=MATCH($A$13&$B$13,$A$2:$A$10&$B$2:$B$10,0)+1


I have assumed there will be only one entry matching the 1-X result. Is this OK ?


Or even simpler :


=AND($A2=$A$13,$B2=$B$13)


Select your data range in column M , and enter the above formula.


Narayan
 
To NARAYANK991:


Perfect! Formula =ROW($M2)=MATCH($A$13&$B$13,$A$2:$A$10&$B$2:$B$10,0)+1 works fine, but another formula doesn't =AND($A2=$A$13,$B2=$B$13). I don't know to apply it


To Debraj Roy:

Well .. interesting formula. It works perfectly. Thanks you but what bothers me is the fact that I don't understand the first part =INDEX(MMULT($G$2:$G$4,TRANSPOSE(H2:H4))/2-3, ....

Would you mind to explain me


Thanks all of u !
 
Hi Madocar!

Lemme try to elaborate.. what you did in your file..


You created "A,B,C" and "1,2,3" two type of data.

Then you MATCH A/B/C.. and again MATCH with 1/2/3 and multiplied both


Where I create a table like below.

[pre]
Code: 
A*1	A*2	A*3
B*1	B*2	B*3
C*1	C*2	C*3
and to create this table I took help from MMULT[/b].. 


Like below..

[code]=MMULT({A,B,C},{1;2;3})

where ABC are Vertical Array & 123 are Horizontal Array..

and to convert 123 from Vertical to horizontal Array I have used TRANSPOSE[/b].


In your result column.. you multiplied it by (* 0.5), I have also used (/2) for the same. then in Column N you have used (-3), so am I.


so Now complete table will looks tike below.

[code](A*1)-2/3	(A*2)-2/3	(A*3)-2/3
(B*1)-2/3	(B*2)-2/3	(B*3)-2/3
(C*1)-2/3	(C*2)-2/3	(C*3)-2/3
[/pre]
with a single formula..


MMULT($G$2:$G$4,TRANSPOSE(H2:H4))/2-3[/code]


I think, Now you can able to understand the Index & Match part. in the below..

{=INDEX(MMULT($G$2:$G$4,TRANSPOSE($H$2:$H$4))/2-3,MATCH($B$13,$F$2:$F$4,0),MATCH($C$13,$F$2:$F$4,0))}[/code]


Regards,

Deb
 
Now I understand. Perfect! Thank you for simple explaining ..

My last question is where did you learn excel? Is there any advice, any guidebook ?


Once again thanks all of you
 
Hi Madocar!


Thanks for the complement..


Whatever I have learn, is only after joining this forum.. follow this site regularly and practice hard..


Regards,

Deb
 
You must log in or register to reply here.
Back
Top