Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

excel lottery

Copper Contributor

need to know how to count skips in a column want to count skips each time i update it.

 

 

 

 

 

7 Replies

@gizmo385 

 

This formula, adjusted for the correct top and bottom row, will count empty cells..

=COUNTIF(A6:A15,"")

 

However, I want to ask what the nature of this spreadsheet is (if you don't mind): In general, even though it might make reading a spreadsheet easier for the human user, that kind of arrangement of data should be saved for the output or reporting end of things. Having empty rows (or empty cells in a column) of data to be processed in some way or other can make the work that Excel has to do more problematic, potentially more error prone.

 

what i wanted it to do is number the skips 1234 then skip@mathetes hits then count and number the skips.

 

 

 

the nature of the spreadsheet is if(or function. it tells me how often a number hits and skips b fore it hits again.

 

To count the number of skips in a column, you can use a formula that counts the number of blank cells in that column. In Google Sheets, the formula to use is =COUNTBLANK(range), where range is the range of cells you want to count the blank cells in. For example, if your column is A and you want to count the blank cells in A1:A100, the formula would be =COUNTBLANK(A1:A100).

You can also use conditional counting formula like =COUNTIF(A1:A100,"") if you want to only count blank cells.

You can then set up a script or macro to automatically update the count each time the column is updated. Read more...

@gizmo385 

Hi

Not sure if that's what you want.
Assume you will have another column to number the skips.
The first cell of the column will be =IF(O4="",1,"")
The second cell of the column will be =IF(O5="",MAX($P$4:P4)+1,"")
Just fix the first cell in the max function range and don't fix the end cell of the range.

Sure! To create a solution that tracks how often a number hits and the skips before it hits again, you'll need a spreadsheet setup that involves a few steps. Here's a simple approach to achieve this:

List Your Numbers: First, you'll need a column where you list the numbers you're tracking. Let's say this is Column A.

Track Hits: In Column B, you'll track each occurrence of the number. You can mark a hit with a 1 or any identifier you choose.

Calculate Skips: In Column C, you'll calculate the number of skips before the number hits again.

Here's a basic formula you can use in Excel or Google Sheets. Assuming you start from row 2:

In Cell B2, you could input =IF(A2=YourNumber,1,0). Replace YourNumber with the specific number you're tracking. This formula checks if the cell in Column A is the number you're tracking and marks it as a hit (1) if it is.

In Cell C2, input =IF(B2=1,0,C1+1). This formula checks if there's a hit in Column B. If there is, it resets the skip count to 0. If there isn't, it adds 1 to the previous skip count.

Copy these formulas down the respective columns for as many rows as you have data.

This setup will give you a simple way to see how often a number hits and the number of skips between hits. Adjust the formulas as needed based on your specific requirements and data structure. Must see I have used table for Lunchtime 2 hot numbers for todayReadmore...

@Steve305 what i need help on is Dan a onstructor in iowa his formula worked finebut microsoft wiped it for me =if(or(B$2=$B1,$C2=H$1,$D2=H$1),H$1,"") this gave me skips between hits,now of course pronlems it worked for F-5 i am missing something?