| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching numbers in Worksheet
In my Worksheet I have 80 columns (80) numbers with over 100 rows. All numbers (1-80) in each row are mixed. I picked 20 random numbers between 1 and 80. My conditional formating formula marks in each row 20 numbers that I picked. The conditional formating formula is: =MATCH(A1,$CC$1:$CC$20,0) I have divided the worksheet in to 2 even parts (40 numbers/columns on each side). I want to know if it is possible to make conditional formating formula to show me how many numbers that I have picked is in first 40 cells and how many numbers is in second lot of 40 cells in the same row. I would like the formula to disply the result at the end of each row. Is it possible? If you need more explanations please let me know. Thanks in advance for your help. John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
Hi!
Try this: For the first 40 columns: =SUMPRODUCT(COUNTIF(A1:AN1,CC$1:CC$20)) For the second 40 columns: =SUMPRODUCT(COUNTIF(AO1:CB1,CC$1:CC$20)) Copy down as needed. Biff "Johncobb45" wrote in message ... In my Worksheet I have 80 columns (80) numbers with over 100 rows. All numbers (1-80) in each row are mixed. I picked 20 random numbers between 1 and 80. My conditional formating formula marks in each row 20 numbers that I picked. The conditional formating formula is: =MATCH(A1,$CC$1:$CC$20,0) I have divided the worksheet in to 2 even parts (40 numbers/columns on each side). I want to know if it is possible to make conditional formating formula to show me how many numbers that I have picked is in first 40 cells and how many numbers is in second lot of 40 cells in the same row. I would like the formula to disply the result at the end of each row. Is it possible? If you need more explanations please let me know. Thanks in advance for your help. John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
P.S.
I'm assuming there are no duplicate numbers in a row or duplicate random numbers. Biff "Biff" wrote in message ... Hi! Try this: For the first 40 columns: =SUMPRODUCT(COUNTIF(A1:AN1,CC$1:CC$20)) For the second 40 columns: =SUMPRODUCT(COUNTIF(AO1:CB1,CC$1:CC$20)) Copy down as needed. Biff "Johncobb45" wrote in message ... In my Worksheet I have 80 columns (80) numbers with over 100 rows. All numbers (1-80) in each row are mixed. I picked 20 random numbers between 1 and 80. My conditional formating formula marks in each row 20 numbers that I picked. The conditional formating formula is: =MATCH(A1,$CC$1:$CC$20,0) I have divided the worksheet in to 2 even parts (40 numbers/columns on each side). I want to know if it is possible to make conditional formating formula to show me how many numbers that I have picked is in first 40 cells and how many numbers is in second lot of 40 cells in the same row. I would like the formula to disply the result at the end of each row. Is it possible? If you need more explanations please let me know. Thanks in advance for your help. John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
Thank you Biff. Well done. It works well. I appreciate your help. Best Regards, John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
You're welcome. Thanks for the feedback!
Biff "Johncobb45" wrote in message ... Thank you Biff. Well done. It works well. I appreciate your help. Best Regards, John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
I have one more question. Is it possible to fill random numbers in a column? I want excel to enter for me 20 random numbers (between 1 and 80) in to a column. Is it possible to do it with one click instead entering them manualy one by one, for example by clicking or filling just one top cell? If you need more explanations please let me know. Regards, John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching numbers in Worksheet
See this:
http://mcgimpsey.com/excel/udfs/randint.html Biff "Johncobb45" wrote in message ... I have one more question. Is it possible to fill random numbers in a column? I want excel to enter for me 20 random numbers (between 1 and 80) in to a column. Is it possible to do it with one click instead entering them manualy one by one, for example by clicking or filling just one top cell? If you need more explanations please let me know. Regards, John -- Johncobb45 ------------------------------------------------------------------------ Johncobb45's Profile: http://www.excelforum.com/member.php...o&userid=16582 View this thread: http://www.excelforum.com/showthread...hreadid=567296 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching numbers in Worksheet? | Excel Worksheet Functions | |||
| Worksheet function for range of numbers | Excel Worksheet Functions | |||
| How can I create a fill that increments based on worksheet numbers | Excel Worksheet Functions | |||
| how can I check a worksheet for duplicate entries or numbers? | Excel Worksheet Functions | |||
| How do I convert numbers into words in excell worksheet? | Excel Worksheet Functions | |||
Linear Mode
