That is no surprise to me.
ISBLANK=0
is non-sensical and returns "#NAME?". COUNTA counts
that as a non-empty value. All is good.
Mike
On 6/25/22 15:05, users+h...@global.libreoffice.org wrote:
Subject:
[libreoffice-users] Calc function COUNTA
From:
Harvey Nimmo
Date:
6/25
Thanks for all the inputs! Great stuff.
As I said, I wanted to count the number of non-blank cells in a column,
but as =COUNTA(A$2:A$1, ISBLANK=0) is above an otherwise empty
column returns the value 1, I am obviously not using the function
correctly. =COUNTA(A$2:A$1) does the trick.
ut the counts the isblank=0 as an item?
On 25 Jun 2022 at 21:41, Harvey Nimmo wrote:
Subject:[libreoffice-users] Calc function COUNTA
From: Harvey Nimmo
To: libreoffice-users
Date sent: Sat, 25 Jun 2022 21:41:23 +0200
I wanted t
nts the isblank=0 as an item?
On 25 Jun 2022 at 21:41, Harvey Nimmo wrote:
Subject: [libreoffice-users] Calc function COUNTA
From: Harvey Nimmo
To: libreoffice-users
Date sent: Sat, 25 Jun 2022 21:41:23 +0200
> I wanted to count
I wanted to count the number of non-blank items in a list of items
using COUNTA.
Cell A1 has the formula =COUNTA(A$2:A$1, ISBLANK=0) above an
otherwise empty column. It returns the value 1!
Is that supposed to mean something? Has anyone notice this?
It's obviously not a big problem,
Like how MONTH(TODAY()) returns the month$ of the year (ie, 1 for
January, 12 for December)...
Is their one?
Or if not, maybe a simple formula I can use to calculate?
Thanks
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems?
Hi,
Am 19.07.2014 15:25, schrieb Tanstaafl:
Is their one?
Yes.
https://help.libreoffice.org/Calc/WEEKNUM
https://help.libreoffice.org/Calc/Date_and_Time_Functions#Functions
Cheers,
Stefan
--
LibreOffice - Die Freiheit nehm' ich mir!
--
To unsubscribe e-mail to:
At 09:25 19/07/2014 -0400, Charles Marcus wrote:
Calc function to return the daynumber weeknumber of the year
Sorry, but I've no idea what a daynumber weeknumber is.
Like how MONTH(TODAY()) returns the month$ of the year (ie, 1 for
January, 12 for December)...
Oddly, you've defined the
At 10:17 11/01/2014 -0800, James E Lang wrote:
Please fill in the blank (___) below with a function name or tell
me there is no such function:
SUM(range) is to SUMIF(range1,criteria,range2) as COUNT(range) is to
___(range1,criteria,range2)
COUNTIF would seem to be the logical answer but I
Sorry, I accidentally sent my message to James instead of the mailing list.
Let me recap what we discussed off-list.
James E Lang wrote:
Correction: I meant SUMIF() rather than SUM().
Corrected formula:
=IF(___(A$3:A7,A7,Q$3:Q7)=___(A$3:A7,A7,R$3:R7),SUMIF(A$3:
On 20/10/13 01:15, Paul wrote:
As I noticed you're practically around the corner from me, I got
interested, and had a look at your spreadsheet :)
[tl;dr: I got solutions, but need some help uploading the file. Also,
it may be that you could work it out in a *much* simpler way, using
what you
On Mon, 21 Oct 2013 14:32:40 +0200
Hylton Conacher (ZR1HPC) hyl...@conacher.co.za wrote:
appreciated especially as he also indicates that using VLOOKUP would
not work, in this case. MIN and MAX are still under testing here :)
Brian indicated that VLOOKUP would work; I don't say different, I'm
Let me rather put it this way: I don't think using VLOOKUP solves the
underlying problem, which is that too much is being done in one
formula.
For any complicated formula, I feel the best solution is either a
custom written user function, or intermediate steps in columns. Hide
those columns, or
Another trick that occurred to me when viewing that spreadsheet was
this one: Some of the column headers contain values that are needed in
calculations. Say for instance you have a column header in cell A1 that
reads Max units 250. You can extract the value (250) with
=VALUE(RIGHT(A1,3))
Or, you
Jay,
On 19/10/13 19:23, Jay Lozier wrote:
Hylton,
If you can breakup the formula into smaller units you might find the
problem.
Often when I have had a similar problem I found the my problem
was I reversed test in an IF clause.
Thanks, I have checked all the IF tests and mathematically they
On 19/10/13 19:32, Jean-Francois Nifenecker wrote:
Hi,
Le 19/10/2013 14:36, Hylton Conacher (ZR1HPC) a écrit :
The initial formula is:
On 19/10/13 20:25, Brian Barker wrote:
At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
Entering a formula into the Function Wizard in Calc shows up two
different answers in the 'Result' and 'Function Result' answer box.
Certainly: there would otherwise be no point in having two boxes.
Hi Paul,
On 21/10/13 00:26, Paul wrote:
Ok, so I just used a file hosting service found via Google search.
Here's the file I modified with my two solutions:
http://www.filedropper.com/electricity_1
It didn't work the first time or two that I tried to download the file
to check, not sure why,
At 17:31 21/10/2013 +0200, Hylton Conacher wrote:
On 19/10/13 19:32, Jean-Francois Nifenecker wrote:
Le 19/10/2013 14:36, Hylton Conacher a écrit :
The initial formula is:
[...]
this is waaay too convoluted to be easily checked.
I would agree, but it should still work.
Any formula that
At 18:40 21/10/2013 +0200, Hylton Conacher wrote:
I like Brian's MIN/MAX idea too but the price might well need to
change on a month, it just depends on when the local authority
increases prices.
That idea is equally applicable to your current arrangement - with
the price band values
I still think the VLOOKUP function can help tremendously but it does, in
practice, need one or two more columns in the lookup table.
The table as it exists has the number of units in each range, rather
than the values at the breakpoints. These breakpoint values are vital,
as they are what
Hi,
Entering a formula into the Function Wizard in Calc shows up two
different answers in the 'Result' and 'Function Result' answer box.
If the editing cursor is placed in a certain section of the formula,
only the 'Function Result' answer box holds the correct answer i.e.
At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
The initial formula is:
Ok, so I just used a file hosting service found via Google search.
Here's the file I modified with my two solutions:
http://www.filedropper.com/electricity_1
It didn't work the first time or two that I tried to download the file
to check, not sure why, but it worked the third time, so maybe
Hi,
Entering a formula into the Function Wizard in Calc shows up two
different answers in the 'Result' and 'Function Result' answer box.
If the editing cursor is placed in a certain section of the formula,
only the 'Function Result' answer box holds the correct answer i.e.
751.52004.
Hylton,
If you can breakup the formula into smaller units you might find the
problem.
Often when I have had a similar problem I found the my problem
was I reversed test in an IF clause.
On Sat, 2013-10-19 at 14:36 +0200, Hylton Conacher (ZR1HPC) wrote:
Hi,
Entering a formula into the
Hi,
Le 19/10/2013 14:36, Hylton Conacher (ZR1HPC) a écrit :
The initial formula is:
At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
Entering a formula into the Function Wizard in Calc shows up two
different answers in the 'Result' and 'Function Result' answer box.
Certainly: there would otherwise be no point in having two
boxes. Suppose you use the Function Wizard to
As I noticed you're practically around the corner from me, I got
interested, and had a look at your spreadsheet :)
[tl;dr: I got solutions, but need some help uploading the file. Also,
it may be that you could work it out in a *much* simpler way, using
what you already have, but a) something is
I am trying to automatically remove a number from a range of cells, if it
appears in a different range.
I am creating a spreadsheet where once a number appears in B4:G11 4 times, it
changes color to Red...got that part.
Then I want to manually enter that number somewhere into J4:N21. once
30 matches
Mail list logo
