iPad – Exclude Errored Cell in SUM or Average Formulas

ipadnumbers

I am working with my timesheet here, and wonder if it is possible to exclude those errored cell from being counted (see I5 cell) either in I23 cell or even better to make I5 blank or 0 if G5 is empty? Same with J5, can it not calculate (J5=H5-E5) if the cell H5 is empty? My Timesheet

Best Answer

Put the below formula exactly as it is in cell I3:

=IF(OR(ISBLANK(E3),ISBLANK(G3)),"",G3−E3)

Then copy & paste it as a formula again to all the cells below and also to the cells to its right and below in the next column except those where you take a sum or average.

The formula calculates the difference in points of time or distances traveled if both of the cells it depends on are non-empty. If either cell is empty, it returns the (None) value which makes the cell appear and get skipped over by the SUM() or AVERAGE() function as empty.

Your Numbers spreadsheet should look like below when you are done:

enter image description here

Note that if you create formulas, simple or not, in other columns whose input contain any of the cells in column I or J, you may need to use in those formulas in other columns the IF() function again but along with the ISNUMBER() function this time.

Related Question