Deleting blank cells in Numbers

Hi Halliday,

Given what Xirasol has said to date, it appears that the upload should consist of a single column of numbers with no empty rows between them. If that's the case, your suggestion of a second table is likely the best solution.

Categories, as you note, are easy to use, and are good for viewing 'by-category' information, but not so good for passing that data to another cell or application.

Here's a start on a possible second table solution, using INDEX to pick off the value in every fourth cell in column C of a sample table, The column C data consists of the sums of the data for the past four quarter hours, recorded in column B.

The formula below the tables is entered in B2 of Table 2 (on the right), and filled down from there.

B2: INDEX(Table 1::C,(ROW()−1)×4+1)

(ROW()-1) gets the row number of the cell containing the formula (2) and subtracts 1. result: 1

×4 multiplies that result by 4. result: 4

+1 adds 1. Result: 5

The result is handed to INDEX, which then returns the value from the fifth cell in column C.

As the formula is filled down, it returns the SUM from every fourth row in column C, compacting the list into a column with no spaces (empty cells) in the list.

Formula on Table 1.

There are two formulas here. The first, in column B is a random number generator that provides a set of 'Data' for this column.

The second is the one that calculates the sum of the data in the four rows of column B ending on the row containing this formula.

B5: SUM(B2:B5)

The formula is filled down by Selecting cells B2-B5, then dragging the Fill control down to the end of column B.

Regards,

Barry

PS: Saw the notice of Yellowbox's post flash by while writing this. Noticed that it also used the second table approach, but thought there were enough differences o continue and post.

B

You can delete whole Rows and whole Columns, Xirasol.

You can select and move cells. (Sort of a "shortcut" of copy/pasting a group of cells, then deleting the "leftovers".)

Would those work for you?

(Sorry. Numbers does not have the Excel feature of "deleting" a block of cells, and having cells below or to the right "move in" to the vacated space.

I actually haven't missed that feature, quite frankly.)

Hi Xirasol,

I'd also like to see a screen shot of the table, if only to answer the question of why you "have columns of 35000 cells" of which "a lot" are blank.

That, and other information coul lead to a solution.

Regards,

Barry

I think I understand, Xirasol.

One of the nice things about Numbers, vs. Excel, for instance, is that you don't have to work on a monolithic sheet of cells!

One way I would approach this would be to create a new Table, that can be placed anywhere that makes the most sense to you, such as right next to the first Table, that references the first Table to produce the second.

You can even resize the Cells so the two Tables line up in a pleasing manner, if you want.

The "hard" part is setting up the calculations, in the second table, to "skip" forward, as you desire.

There are multiple techniques, depending upon your needs for flexibility, and such.

A simple way is to create the desired formulas (formulae) in the first two cells, then extending that to your entire column (in the second Table, with columns of 8750 rows, of course).

Does this sound like a reasonable solution?

(It's also possible that using merged cells, in the same Table, one could obtain the desired affect; but that will depend upon how you transfer the data "in to the analysis program [you are] using."

Using a separate, simple Table, would seem like the most straightforward approach for practically any analysis program.)

Hi Xirasol,

SUMIFS in another table is your friend.

A small subset of the data might look like this:

For the Value column I made up some simplified data.

I separated the dates and hours (24 hour clock) into 2 columns so that SUMIFS can follow the syntax:

SUMIFS(sum-values, test-values, condition, test-values…, condition…)

Formula in B2 of the second (and much shorter) table

=SUMIFS(Data::B,Data::C,"="&C2,Data::D,"="&D2)

Fill down.

Regards,

Ian.

Hi Xirasol,

We can not see what you see. Please post a screen shot of a small part of your table.

Delete or hide personal or sensitive data before you take the screen shot. Click on the table to select it so that the screen shot shows the column labels (A, B, C...) and the row labels (1, 2, 3...) and especially the header rows. What formula is adding every fourth value? Do you want a total for each hour? Where does this formula reside?

See this User Tip by Wayne on how to post a screen shot.

How to Post a screenshot to the Apple Discussion Forums

Regards,

Ian.

Barry:

Xirasol's statement that he would "then have a lot of blank cells which cannot be up boded[uploaded] in to the analysis program I am using" pertains to his operation of "add[ing] every 1st to 4th (15 minute interval) in to a new column( 60 minute)", of non-overlapping hours, which will leave three (3) out of every four (4) cells Blank.

That's 3/4ths of the "columns of 35000 cells" that will be Blank: useless to the analysis program.

All that, of course, would be the case if he were to perform the computations within the original grid of cells, using the approach he was thinking of.

(Of course, one could perform the operation I proposed within the original Table, but, then, the correspondence with the rest of the Table will be lost, as each new datum is shifted up by four cells. [However, that would be a rather typical Excel-like solution.]

Additionally, these hourly sums could be done using Categories, but that will be, likewise, difficult to transfer into another analysis program.)

Yes, Yellowbox.

SUMIFS is very flexible! (I use them "all the time" in some projects I'm doing, these days.)

However for the, apparently, far more regular data Xirasol seems to be working with, for which we know nothing of the other columns (so far), and which may actually involve multiple columns of data that need to be processed in the same manner, I figured that an approach that took advantage of the regular nature of the data would make more sense, especially to Xirasol.

I kind of like the use of INDEX, you have their, Barry.

I know, however, that there is a solution that doesn't require the use of any "summation" columns(s) within the original Table.

On the other hand, your use of INDEX got me thinking that a solution using OFFSET might be better than what I was originally thinking!

By the way, Yellowbox, you are aware that prepending the string "=" is not necessary in the ...IF and ...IFS functions: the "=" comparison operator is assumed, if not present.

(That being said, there is a disconnect between how Numbers handles NULL strings [""] with vs. without the "=" comparison operator, compared to how Excel, LibreOffice/OpenOffice Calc, and Google Sheets handle such! It makes it so Numbers has no way [that any of us have found] to match cells with a NULL string value!)

Hi Halliday,

Halliday wrote:

By the way, Yellowbox, you are aware that prepending the string "=" is not necessary in the ...IF and ...IFS functions: the "=" comparison operator is assumed, if not present.

You are right. Thank you!

Regards,

Ian.

Hi Halliday,

I got syntax errors until I used "="& in the conditions.

I am never sure when Numbers requires quotes in a formula!

Regards,

Ian.

The quotes, Yellowbox, around the equal sign, "=", is required.

It's the appending of that string ("="&), in the condition, that is not required.