mardi 3 septembre 2019

Is there a way to count blank cells, but only if they are between cells with values, in Google Sheets?

I am trying to build a google sheet that allows the user to input the shape and size of an array of solar panels, each cell representing a panel, and it outputs the hardware needed to be based on the cells filled in.

For example, between two panels you need two of Widget A. At the end of each row, or where there is an empty space between panels, you need two of Widget B for each panel. This is easy when the array has no empty spaces in a row, as my formula for that is:

Widget A =if(counta(b2:u2)=0, 0, (counta(b2:u2)-1)*2)

Widget B =if(counta(b2:u2)=0, 0, 4)

However, if the row of panels has an empty space in it, these formulas do not see the gap and think that Widget A should go-between rather than actually needing Widget B.

I cannot find a way to find if there is a blank cell between filled cells, and count how many there are.

I have tried varying formulas involving filter, isblank, columns, countblank, index, etc to no luck as they can tell me the value of the first filled cell and the value of the last filled cell, or the number of filled cells, but cannot tell me how many blank cells are in between filled cells in a row.

The below returns the value of the last filled cell:

=INDEX( FILTER( B2:U2 , NOT( ISBLANK( B2:U2 ) ) ) , COLUMNS( FILTER( B2:U2 , NOT( ISBLANK( B2:U2 ) ) ) ) ) 

and the below find the value of the first filled cell:

=+filter(B2:U2,B2:U2<>"")

But neither allows me to find the blank cell in the middle.

Here is a copy of what I am working on:

https://docs.google.com/spreadsheets/d/1wz3P5giVp0JlneYUZdW6zVPck5tkdohmbnnP_zIfptE/edit?usp=sharing

I would like the count of Widget A to be 12 and the count of Widget B to be 8, but instead, it is 14 and 4 because it does not recognize the gap at H2.

Aucun commentaire:

Enregistrer un commentaire