Posts by 1legin

1legin · Feb 20th 2019

I got it using =COUNTIF(H2:INDIRECT(CONCATENATE("L",$S$3)),O3) thanks for pointing me in the right direction Ali

Nigel

:ole:

1legin · Feb 20th 2019

I have searched the net for help with INDIRECT function without any luck so far
Nigel

1legin · Feb 20th 2019

I have searched the net for help with INDIRECT function without any luck so far

Nigel

1legin · Feb 20th 2019

Lets say i have numbers in 5 columns H - M and the number to count in range is in AD3
is there a way to determin the range depth "$M$9" by using an input cell "AH3", so if i enter 100 in cell "AH3"
The formula will change from

=COUNTIF($H$2:$M$9,AD3)
To

=COUNTIF($H$2:$M$100,AD3)

Thanks
Nigel

1legin · Jun 25th 2018

I am trying to convert the formula in attached worksheet cell "P2" Draws tab to count Pairs, I dont know if this is possible with this formula,

Thanks in advance

Nigel

1legin · May 20th 2018

Hi

Iam trying to find a formula to get the number of times a number appears on a given day, Please see below

Code

This is just a small section of the page


Trying to find  totals for Fri & Tue for each number


   [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 273"]
 	 		[TR]
 			[TD="class: xl63, width: 33"] [/TD]
 			[TD="class: xl63, width: 27"] [/TD]
 			[TD="class: xl63, width: 24"] [/TD]
 			[TD="class: xl63, width: 24"] [/TD]
 			[TD="class: xl63, width: 24"] [/TD]
 			[TD="class: xl63, width: 24"] [/TD]
 			[TD="class: xl63, width: 21"] [/TD]
 			[TD="class: xl63, width: 21"] [/TD]
 			[TD="class: xl63, width: 10"] [/TD]
 			[TD="class: xl63, width: 38"] [/TD]
 			[TD="class: xl63, width: 38"] [/TD]
 			[TD="width: 39"] [/TD]
 			[TD="class: xl63, width: 39"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl65"]Day[/TD]
 			[TD="class: xl65"]N1[/TD]
 			[TD="class: xl65"]N2[/TD]
 			[TD="class: xl65"]N3[/TD]
 			[TD="class: xl65"]N4[/TD]
 			[TD="class: xl65"]N5[/TD]
 			[TD="class: xl65"]L1[/TD]
 			[TD="class: xl65"]L2[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl65"]No.'s[/TD]
 			[TD="class: xl65"]All[/TD]
 			[TD="class: xl65"]Fri[/TD]
 			[TD="class: xl65"]Tue[/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]5[/TD]
 			[TD="class: xl64"]8[/TD]
 			[TD="class: xl64"]10[/TD]
 			[TD="class: xl64"]13[/TD]
 			[TD="class: xl64"]31[/TD]
 			[TD="class: xl64"]3[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]1[/TD]
 			[TD="class: xl63"]9[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]4[/TD]
 			[TD="class: xl64"]16[/TD]
 			[TD="class: xl64"]20[/TD]
 			[TD="class: xl64"]31[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]2[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]2[/TD]
 			[TD="class: xl63"]4[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]3[/TD]
 			[TD="class: xl64"]22[/TD]
 			[TD="class: xl64"]34[/TD]
 			[TD="class: xl64"]49[/TD]
 			[TD="class: xl64"]50[/TD]
 			[TD="class: xl64"]8[/TD]
 			[TD="class: xl64"]11[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]3[/TD]
 			[TD="class: xl63"]10[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]17[/TD]
 			[TD="class: xl64"]25[/TD]
 			[TD="class: xl64"]35[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]44[/TD]
 			[TD="class: xl64"]2[/TD]
 			[TD="class: xl64"]5[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]4[/TD]
 			[TD="class: xl63"]9[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl64"]23[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]45[/TD]
 			[TD="class: xl64"]9[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]5[/TD]
 			[TD="class: xl63"]10[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl64"]15[/TD]
 			[TD="class: xl64"]17[/TD]
 			[TD="class: xl64"]42[/TD]
 			[TD="class: xl64"]48[/TD]
 			[TD="class: xl64"]4[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl63"]9[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl64"]24[/TD]
 			[TD="class: xl64"]40[/TD]
 			[TD="class: xl64"]41[/TD]
 			[TD="class: xl64"]46[/TD]
 			[TD="class: xl64"]5[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]7[/TD]
 			[TD="class: xl63"]6[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]20[/TD]
 			[TD="class: xl64"]23[/TD]
 			[TD="class: xl64"]28[/TD]
 			[TD="class: xl64"]30[/TD]
 			[TD="class: xl64"]44[/TD]
 			[TD="class: xl64"]3[/TD]
 			[TD="class: xl64"]7[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]8[/TD]
 			[TD="class: xl63"]10[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]3[/TD]
 			[TD="class: xl64"]16[/TD]
 			[TD="class: xl64"]25[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]44[/TD]
 			[TD="class: xl64"]7[/TD]
 			[TD="class: xl64"]11[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]9[/TD]
 			[TD="class: xl63"]10[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]15[/TD]
 			[TD="class: xl64"]27[/TD]
 			[TD="class: xl64"]33[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]50[/TD]
 			[TD="class: xl64"]4[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]10[/TD]
 			[TD="class: xl63"]10[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]5[/TD]
 			[TD="class: xl64"]25[/TD]
 			[TD="class: xl64"]34[/TD]
 			[TD="class: xl64"]48[/TD]
 			[TD="class: xl64"]50[/TD]
 			[TD="class: xl64"]6[/TD]
 			[TD="class: xl64"]7[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]11[/TD]
 			[TD="class: xl63"]9[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Tue[/TD]
 			[TD="class: xl64"]14[/TD]
 			[TD="class: xl64"]27[/TD]
 			[TD="class: xl64"]39[/TD]
 			[TD="class: xl64"]46[/TD]
 			[TD="class: xl64"]48[/TD]
 			[TD="class: xl64"]11[/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]12[/TD]
 			[TD="class: xl63"]12[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 		[TR]
 			[TD="class: xl64"]Fri[/TD]
 			[TD="class: xl64"]1[/TD]
 			[TD="class: xl64"]29[/TD]
 			[TD="class: xl64"]33[/TD]
 			[TD="class: xl64"]45[/TD]
 			[TD="class: xl64"]47[/TD]
 			[TD="class: xl64"]4[/TD]
 			[TD="class: xl64"]8[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl64"]13[/TD]
 			[TD="class: xl63"]8[/TD]
 			[TD="class: xl63"] [/TD]
 			[TD="class: xl63"] [/TD]
 		[/TR]
 	 [/TABLE]

Display More

Thanks

Nigel

1legin · May 7th 2018

Trying to get the formula for count of pairs in range and display in chart below.

Thanks

Nigel

RANGE CHART
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Image1.jpg","data-attachmentid":1203328}[/ATTACH]

1legin · May 2nd 2018

Trying to get this code to add a new row in excel via a button, not the full row, just the first 10 cells.
Can anyone amend the code for me please

Thanks

Nigel

Code

Sub Button1_Click()




Rows("6:6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A7:A8").Select
Selection.AutoFill Destination:=Range("A6:A8"), Type:=xlFillDefault




Range("A8:J8").Select
Selection.Copy
Range("A6:J7").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


Range("A7:A8").Select
Selection.AutoFill Destination:=Range("A6:A8"), Type:=xlFillDefault




Range("A8:J9").Select
Selection.Copy
Range("A6:J7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Worksheets("Sheet1").Columns("A:J").AutoFit


Range("A6").Select
End Sub

Display More

Sorry for not adding Tag's

1legin · Apr 18th 2018

Works great Thanks Alan

1legin · Apr 18th 2018

Hi Alan,

Thanks for the answer to my question,How would i implement this ? as i am no good with VBA.

Thanks again

Nigel

1legin · Apr 17th 2018

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]B[/TD]
[TD="class: xl64, width: 64"]C[/TD]
[TD="class: xl64, width: 64"]D[/TD]
[TD="class: xl64, width: 64"]E[/TD]
[TD="class: xl64, width: 64"]F[/TD]
[TD="class: xl64, width: 31"]G[/TD]
[TD="class: xl64, width: 64"]H[/TD]
[TD="class: xl64, width: 64"]I[/TD]

[/tr]

[tr]

[TD="class: xl64"]Row No.[/TD]
[TD="class: xl64"]N1[/TD]
[TD="class: xl64"]N2[/TD]
[TD="class: xl64"]N3[/TD]
[TD="class: xl64"]N4[/TD]
[TD="class: xl64"]N5[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]No.'s[/TD]
[TD="class: xl64"]Enter No.[/TD]

[/tr]

[tr]

[TD="class: xl63"]1[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]17[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]2[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]21[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]4[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]5[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]16[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]21[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]6[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl63"]38[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]7[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]28[/TD]
[TD="class: xl63"]35[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]8[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl63"]32[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]9[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]34[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]10[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]29[/TD]
[TD="class: xl63"]33[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]11[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]25[/TD]
[TD="class: xl63"]32[/TD]
[TD="class: xl63"]38[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]12[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]26[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl64"] [/TD]

[/tr]

[tr]

[TD="class: xl63"]13[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl63"]19[/TD]
[TD="class: xl63"]33[/TD]
[TD="class: xl63"]37[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl64"]
[/TD]

[/tr]

[/TABLE]
Hi all,

I am trying to work out the frequancy for numbers in columns B to F.
So there is a total of 500 rows "B1:F500", i would like the answers to be in column I, but
instead of having results for all rows, i would like to enter a number in cell "Enter No."
and have the results for that many rows.
Not sure if this is possible with countifs or drop down box etc ..

Thanks

Nigel

1legin · Apr 11th 2018

That's Fantastic Ali, works great.

Thanks for the help

Nigel

1legin · Apr 11th 2018

I have attached file for you to take a look at [ATTACH]n1202267[/ATTACH]

1legin · Apr 11th 2018

I am trying to work out the interval between apperences of a number in a lottery game [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 240"]

[tr]

[TD="class: xl65, width: 64"] N1[/TD]
[TD="class: xl65, width: 64"]N2[/TD]
[TD="class: xl65, width: 64"]N3[/TD]
[TD="class: xl65, width: 64"]N4[/TD]
[TD="class: xl65, width: 64"]N5[/TD]

[/tr]

[tr]

[TD="class: xl66"]18[/TD]
[TD="class: xl67"]19[/TD]
[TD="class: xl68"]29[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]32[/TD]
[TD="class: xl65"]38[/TD]

[/tr]

[tr]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]31[/TD]

[/tr]

[tr]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]36[/TD]

[/tr]

[tr]

[TD="class: xl65"]16[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl65"]28[/TD]
[TD="class: xl65"]34[/TD]
[TD="class: xl65"]36[/TD]

[/tr]

[tr]

[TD="class: xl65"]14[/TD]
[TD="class: xl67"]19[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl69"]33[/TD]
[TD="class: xl70"]37[/TD]

[/tr]

[tr]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]23[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl68"]29[/TD]

[/tr]

[/TABLE]

Using just the top row for this example
No. 18 last appered 5 games ago
No. 19 last appered 3 games ago and then 6 games ago
No. 29 last appered 7 games ago
No. 33 last appered 3 games ago and then 6 games ago
No. 37 last appered 3 games ago and then 6 games ago

I am hoping to show answers in a columns " 1st time out, 2nd time out" etc...

Hope you can understand what i am trying to explain

Thanks

Nigel

Posts by 1legin

countif using input cell as range depth

countif using input cell as range depth

countif using input cell as range depth

countif using input cell as range depth

Pairs of numbers last out by date

Countifs?

Trying to find pairs

Add new cells in "range" Row

Help with COUNTIFS

Help with COUNTIFS

Help with COUNTIFS

Count since last out

Count since last out

Count since last out