Advanced excel formula.?

Question

I have conditional formating that changes a cells colour when a type in a number between 1, 2 and 3. This applies to a range of cells inside a box. What formula could i use to find the maximum number of consecutive blank cells in this box that go along the rows. What I'm trying to do is find the total consecutive empty seats going along each row. 1, 2 and 3 obviously mean the seat isn't empty.

sorry i meant is empty

NO I MEAN IT ISN'T. :D

Answer 1

Create another column. In the first row holding a value enter 1 in the adjacent column. In B3 (this assumes the first value 1 is in B2) use this formula "=IF(ISBLANK(A3),0,B2+1)". Where A is the column you are referencing, then copy down the column to the last adjacent entry. Use this formula to determine maximum number "=max(B:B)" (do not put formula in the B column).

This is what the formulas do. In "=IF(ISBLANK(A3),0,B2+1)" asks if the adjacent cell is BLANK then gives 0 as a value otherwise adds 1 to the previous number.
"MAX(B:B)" gives the maximum number in the B column.

Answer 2

To get the number of blank cells in row 1:
=COUNTBLANK(1:1)

There are 256 cells in a row, so if the entire row is blank, that's the number you'll get. If you want a range of cells, like A1 through E1, use:

=COUNTBLANK(A1:E1)

Answer 3

counseled approach - use PivotTable from Microsoft's help: count quantity how usually numerous values take place via making use of a PivotTable rfile you need to use a PivotTable rfile to demonstrate totals and to count quantity the occurrences of unique values. decide on the column containing the guidance. on the guidance menu, click PivotTable and PivotChart rfile. click end. Drag the column label from PivotTable container checklist to Drop Row Fields right here. Drag a similar column label from PivotTable container checklist to Drop archives products right here. be conscious in case your archives includes numbers, the PivotTable rfile totals the entries rather of counting them. to alter from the Sum precis function to the count quantity precis function, on the PivotTable toolbar (toolbar: A bar with buttons and strategies which you employ to accomplish instructions. To demonstrate a toolbar, press ALT and then SHIFT+F10.), click container Settings , and then interior the Summarize via container, click count quantity. 2nd approach: =COUNTIF(A2:A7,"Buchanan") =count quantity(IF((A2:A11="South")*(C2:C11="Me... the two are basically examples, yet you will possibly specify a style and count quantity the cases of "Y" or "N" between "M" and "F." the right syntax of the function provides you with the solutions you're searching for for.