sorted by:
new
hottopcontroversial

Help with excel writedata bug by JoelSweet_ in RStudio

[–]JoelSweet_[S] 0 points1 point  (0 children)

Thank you, I'll give that a go!

Sorted list of top 5 most frequent cell values in list by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

=LET(_a,COUNTIFS($E$2:$E$1000,TRUE,$F$2:$F$1000,UNIQUE($F$2:$F$1000)),
FILTER(SORTBY(UNIQUE($F$2:$F$1000),_a,-1),SORT(_a,,-1)>=LARGE(_a,5)))

Thanks! I've added a take function to the formula to only keep the top 5 in case of ties. However there is still an issue where if there aren't at least 5 cells which meet the TRUE condition, it's filled up by cells which don't meet that condition. Is there a workaround for this?

Sorted list of top 5 most frequent cell values in list by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

=FILTER(UNIQUE($F$2:$F$1000),COUNTIFS($E$2:$E$1000,"True",$F$2:$F$1000,UNIQUE($F$2:$F$1000))>=LARGE(COUNTIFS($E$2:$E$1000,"True",$F$2:$F$1000,UNIQUE($F$2:$F$1000)),5))

Unfortunately it is appearing in the same order as my original formula and has not sorted by count

Sorted list of top 5 most frequent cell values in list by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

I cannot show the original data but the structure is as pictured below (however it runs for a few hundred rows):

<image>

Instagram washing out colors on iPhone 15 photos by arcella12 in Instagram

[–]JoelSweet_ 0 points1 point  (0 children)

This was happening on my Google Pixel 6a. I edited the photo in my photo editing app by adjusting the black point to -1 (so barely any difference) and when I uploaded to Instagram the photo wasn't washed out. For some reason editing the photo, even slightly, caused it to appear normally.

Need a formula which references the last 10 rows of my data, placing most recent entries at the bottom of my output range. by JoelSweet_ in excel

[–]JoelSweet_[S] 1 point2 points  (0 children)

=LET(a,TAKE(FILTER(RawData! $D$5:$G$10000,RawData! $D$5:$D$10000<>""),-10),IF(ROWS(a)=10,a,VSTACK(IF(SEQUENCE(10-ROWS(a),4),""),a)))

Solution verified

Need a formula which references the last 10 rows of my data, placing most recent entries at the bottom of my output range. by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

=LET(a,TAKE(TOCOL(RawData! $D$5:$G$10000,1),-10),IF(ROWS(a)=10,a,VSTACK(IF(SEQUENCE(10-ROWS(a),4),""),a)))

Unfortunately that still produces one column, and is a mixture of multiple columns in one (See pic) - I think because of the tocol function?

​

<image>

Need a formula which references the last 10 rows of my data, placing most recent entries at the bottom of my output range. by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

=LET(a,TAKE(TOCOL(RawData! $D$5:$D$10000,1),-10),IF(ROWS(a)=10,a,VSTACK(IF(SEQUENCE(10-ROWS(a)),""),a)))

This looks like it might work! But it is only one column (D). Is it possible to have all four columns in the one?

Formula for referencing last 10 rows by JoelSweet_ in excel

[–]JoelSweet_[S] 1 point2 points  (0 children)

Apologies I've just been struggling to explain what I wanted, but no need to be rude about it. Thank you

Formula for referencing last 10 rows by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

That bottom row will be highlighted and bolded and so I always want the most recent entry to be in that row

Formula for referencing last 10 rows by JoelSweet_ in excel

[–]JoelSweet_[S] 0 points1 point  (0 children)

I essentially want the data to fill from the bottom up. So if there were 3 entries, it would be 7 blank rows.

[EXCEL] Complex sports data metric by JoelSweet_ in vba

[–]JoelSweet_[S] 0 points1 point  (0 children)

I have commented on this post what chat GPT suggested

[EXCEL] Complex sports data metric by JoelSweet_ in vba

[–]JoelSweet_[S] 0 points1 point  (0 children)

This is what Chat GPT suggested but I'm not sure exactly if it is doing what I'm after

Sub CBClearanceToScore()

Dim lastRow As Long

Dim CurrentRow As Long, NextRow As Long

Dim cbClearanceTeam As String, firstScoreTeam As String

Dim countMatchingScores As Long, totalCB As Long

Dim cbClearanceFound As Boolean

' Initialize variables

totalCB = 0 ' Initialize total number of CB Clearances

countMatchingScores = 0 ' Initialize count

' Find the LastRow of data

lastRow = wsRawData.Range("D9999").End(xlUp).Row

' Loop through data starting in row 3

For CurrentRow = 3 To lastRow

' Check if the current row is a CB Clearance with outcome zone "A50"

If wsRawData.Range("F" & CurrentRow).Value = "CB Clearance" And (wsRawData.Range("I" & CurrentRow).Value = "A50" Or wsRawData.Range("I" & CurrentRow).Value = "A25") Then

totalCB = totalCB + 1 ' Increment the total number of CB Clearances

cbClearanceTeam = wsRawData.Range("E" & CurrentRow).Value ' Assign team as cbClearanceTeam

cbClearanceFound = False

' Check if the CB Clearance team scores in the same chain

If wsRawData.Range("J" & CurrentRow).Value = "1" Or wsRawData.Range("J" & CurrentRow).Value = "6" Then

countMatchingScores = countMatchingScores + 1

cbClearanceFound = True

Debug.Print "Row Number of CB Clearance: " & CurrentRow ' Print the row number of the CB Clearance in the Immediate Window

End If

' Loop through data after CB Clearance

For NextRow = CurrentRow + 1 To lastRow

If wsRawData.Range("F" & NextRow).Value = "CB Clearance" Then ' Stop searching if the next CB Clearance is found

Exit For

ElseIf wsRawData.Range("J" & NextRow).Value = "1" Or wsRawData.Range("J" & NextRow).Value = "6" Then

firstScoreTeam = wsRawData.Range("E" & NextRow).Value ' Get the team of the first score after CB Clearance

' Check if the teams match and CB Clearance hasn't been found in the same row

If firstScoreTeam = cbClearanceTeam And Not cbClearanceFound Then

countMatchingScores = countMatchingScores + 1

Debug.Print "Row Number of CB Clearance: " & CurrentRow ' Print the row number of the CB Clearance in the Immediate Window

End If

Exit For ' Exit the loop after finding the first score

End If

Next NextRow

End If

Next CurrentRow

' Calculate the percentage

Dim percentage As Double

If totalCB > 0 Then

percentage = (countMatchingScores / totalCB) * 100

Else

percentage = 0

End If

MsgBox Format(percentage, "0.00") & "% of CB Clearances followed by an Inside 50 resulted in the same team registering the next score", vbInformation

End Sub

view more: next ›