Re: [libreoffice-users] CALC - How to count blank cells?

El 09/07/12 3:31, Simon Cropper escribió:
> On 09/07/12 11:01, Michael Reich wrote:
> > On 7/4/12 11:00 AM, users+help@global.libreoffice.org wrote:
> > > Subject:
> > > Re: [libreoffice-users] CALC - How to count blank cells?
> > > From:
> > > MiguelAngel <mariosv@miguelangel.mobi>
> > > Date:
> > > 7/4/12 3:44 AM
> > >
> > > To:
> > > users@global.libreoffice.org
> > >
> > >
> > > El 04/07/12 5:16, dave wagner escribió:
> > > > I have a small range of cells (less than 100) in which I need to
> > > > count the
> > > > cells that are blank.
> > > > What is the best way to do this?
> > > >
> > > > I was trying something like "=COUNTIF(C11:C103,(CELL("Type",b)))".But
> > > > that
> > > > just did not work.
> > > >
> > > > Thanks to all who helps me with this simple problem.
> > >
> > > =SUMPRODUCT(ISBLANK(C11:C103))
> > >
> > > Miguel Ángel.
> > In a related (I think) question, what formula could be used to identify
> > the last row in a spreadsheet with data in it?   It's a similar
> > situation as the OP, in which new data is inserted as a new row at the
> > bottom of the sheet.   I want another worksheet to be able to pick up
> > certain fields from the last row of data, but haven't been able to
> > figure out how to locate which row is last (with data).
> >
> > A1=a
> > A2=b
> > A3=c
> > A4=d
> >
> > What I'm looking for is a formula that would tell me, in the above data,
> > that row 4 is the last one with data.
> >
> > Thanks for any help!
>
> I know that this is not what you asked for exactly, but coupled with
> CELL() or COUNTBLANK() function, might allow you to establish the row
> number in your situation.
>
> The following allows me to get the last value of the column (F) in
> sheet1 (used in my case in a summary table that lists the final value in
> a column that lists the running balance of my bank account)...
>
> =OFFSET($'sheet1'.$F$6,COUNTA($'sheet1'.$F$6:$'sheet1'.$F$9633)-1,0)
>
> Assumes the range $F$6: to $F$9633 has continuous data - essentially it
> returns the number of rows/cells with data, so will not work if your
> array or range has blanks (unless the number is static or can be
> predicted).
>
> So in your example... COUNTA(A1:A1000) will return 4, knowing that you
> start from A1 allows you to extrapolate that the last row is A4.

=SUMPRODUCT(MAX(NOT(iSBLANK(c11:c999))*ROW(c11:c999)))

give the number of the last row with data in the range.

Miguel Ángel.


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted