Functions in criteria expressions ?

Discuss the spreadsheet application
Post Reply
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Functions in criteria expressions ?

Post by Richarda44 »

Can functions (such as Isformula() ) be used in criteria expressions and if so how ? In Lotus Symphony I was able to concatenate all sorts of functions, ranges and math symbols into one label to make a criteria or line of macro code. Has progress gone backwards ?
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Functions in criteria expressions ?

Post by jrkrideau »

Has progress gone backwards ?
Well AOO is not a Microsoft product but I suppose it is still possible.
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Functions in criteria expressions ?

Post by acknak »

Richarda44 wrote:Can functions (such as Isformula() ) be used in criteria expressions and if so how ? ...
Sorry, no. Calc follows Excel, which supports only values or simple text expressions (comparisons).

If you need full expressions, you can either make the formula an array formula, or use SUMPRODUCT, which provides array context by default.
AOO4/LO5 • Linux • Fedora 23
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Functions in criteria expressions ?

Post by Lupp »

@Richarda44:
Would you mind to post a complete formula of the kind you see an issue with - and to make clear if you are talking about an application with a Calc-DB function or one with ordinary Calc functions?

There is a significant difference in the usage of 'Criterion' for ordinary functions and 'Criteria' with DB-functions.

I simply cannot find a plausible reason to include something like an ISFORMULA expression in a 'Criterion' used with an ordinary function like COUNTIF.

Using ISFORMULA in the first parameter place of COUNTIF e.g. will only make sense under array evaluation. ISFORMULA does, however not support this feature presently. This seems to be an old common bug in OOo (AOO) and LibO as well.

For LibreOffice I reported this bug as https://bugs.documentfoundation.org/sho ... i?id=91502. It is confirmed but still open there. My final (to date) example there also demonstrates a workaround.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Re: Functions in criteria expressions ?

Post by Richarda44 »

Thank you both.

Reason I want to 'countif' cells without formulae is :- I record daily two 6 digit meter readings on a spreadsheet. I find it difficult to reliably remember them by the time I get back to my computer :? So the whole column has formulae based on the previous reading which works out the first 3 digits so all I have to remember are the last 3 which for me is comparatively much easier. I want to count the number of numbers I have actually typed in.

The solutions given me in the other thread I can get to work but I often struggle putting criteria within functions like countif and would like to learn more if I am missing something. I do use the DB functions but I miss the ability to put criteria directly into the function as in Symphony and L123.
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Functions in criteria expressions ?

Post by Lupp »

Richarda44 wrote:The solutions given me in the other thread...
What thread?
Richarda44 wrote:Reason I want to 'countif' cells without formulae is :- ... I want to count the number of numbers I have actually typed in.
I don't get this together.
Richarda44 wrote:...I often struggle putting criteria within functions like countif and would like to learn more...
I don't think there is much to learn aobut this except
- (1) - There are some functions containing bugs. This remains true where the 'Criterion' concept comes in.
- (2) - The 'Criterion' parameter always passes text to the function's body. As the comparison often is made with numeric values passed via other parameters, the body of the function will have to apply another conversion. There may occur all the errors related to automatic type conversion - in specific to the round-trip-problem - unavoidably related to it. There is no way to make decimal representation and dyadic representation ('Double' e.g.) of numbers round-trip-proof because the base 10 contains the prime 5. Therefore the algorithms will have to implement a small allowance in comparisons. During some experiments a few years ago I managed to get 143 numbers out of 1000 generated by a simple formula which were judged to be ">" (GT) than themselves by COUNTIF.
- (3) - Where feasible at all we should prefer solutions omitting the criterion concept.

Would you mind to post a (some) complete formula(e) of the kind you see an issue with - and to make clear if you are talking about an application with a Calc-DB function or one with ordinary Calc functions?

I still am not sure if I got your problem exactly. (Cf. second quotation's comment.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Functions in criteria expressions ?

Post by Lupp »

Meanwhile there was a new post in another thread concerning the same issue but ceated earlier by @Richarda44. (As I cannot draw valid conclusions from a pdf I did not study it in detail. I do also not know why and when the OQ has marked that other post SOLVED. It's irritating.)

Back to this thraed:
I did a bit more of testing and had to find again that AOO and LibO have developed differently to some extent now. As far as specific at all my comments were based on my knowledge of the behaviour of LibO which I presently use mainly in V 5.2.2.2 . To avoid misunderstandings I made a new demo, and I have to state now:
(1) LibO V5.2 and AOO V4.1.2 both are buggy with respect to the present issue.
(2) The workaround I demonstrated in an attachment made to my mentioned bug report concerning LibO does not work in AOO.

To demonstrate these statements I attach the new example made with LibO and also (a rare exception) an image showing how it works in LibO. Specifically regard D5:D6, D10, F4, D24:D30, and F28 in the image and compare the view with what you get when the .ods is opened in AOO.

It's a kind of a mess. I will not again post into this thread.

Just another word: I still would like to see a few example formulae @Richarda44 actually had the problem with.
Attachments
aoo86435_Demonstrations_1b.jpg
aoo86435Demonstrations_1.ods
(19.93 KiB) Downloaded 60 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

[solved sort of] Re: Functions in criteria expressions ?

Post by Richarda44 »

Thanks again both.
@Lupp my understanding is far less than yours. Arrays and pivot tables I have never understood and I usually look by experiment for something very simple that works for me.
The formula I could not get to work was:- =COUNTIF(L714:L717;NOT(ISFORMULA(L714:L717))) - I see the flaws - Isformula only accepts one cell ranges and countif needs a different criteria as @acknak explained. I marked my first thread [solved] because I can get the number I want using a helper column. The reply posting a pdf file did not help my learning because I could not see any formulae!

I find criteria tricky because I can never remember when to use "quotes".
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Functions in criteria expressions ?

Post by Villeroy »

The most simple solution: Copy down ISFORMULA(single_cell) and get the sum of the boolean values. Each True value is equivalent to number 1, each False value is equivalent to 0.

Alternative: TYPE(single_cell)=8 and get the sum of that.

Array formula {=ISFORMULA(range)} should return as many True/False values as there are cells in range but it does not. Same problem with {=TYPE(range)=8}. May be a bug. May be that these functions are not intended to work in array context. I don't know.

{=FORMULA(range)} works fine. It returns the formula of each cell in range or #VALUE! if there is no formula.
{=ISTEXT(FORMULA(range))} returns as many True/False as there are cells in range.
{=SUM(ISTEXT(FORMULA(range)))} sums up the above array formula returning the count of formula cells.

Regarding the pivot tables:
Data Pilot wiki wrote:Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.
A pivot table is the most simple solution for about one third of all problems on this forum because many of todays spreadsheet users try to mis-use Calc as a database and the pivot table is the best accomodation. You hardly find any mathematical questions but lots of questions about lists and how to summarize lists. Another third of problems on this forum could be solved by pivot tables if people would keep their data in simple lists and not in cross tables or mulitple tables for each category.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Functions in criteria expressions ?

Post by Villeroy »

please delete
Last edited by Villeroy on Sat Dec 10, 2016 1:13 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Functions in criteria expressions ?

Post by Lupp »

Richarda44 wrote:...The formula I could not get to work was: =COUNTIF(L714:L717;NOT(ISFORMULA(L714:L717))) ...
OK your criteria are using an array (part of a column) in place of a single ("scalar") parameter. Calc can resolve this in two very different ways:
(1) Intersection mode (standard): Only the one element contained in the same row as the formula itself is evaluated.
(2) Array mode, aka "iterative" (applied if the formula was entered with Ctrl+Shift+Enter and also if it is embedded in a position where an array is expected): The formula calculates a value for every one of the elements of the range and returns an array formed from these many results.

Obviously neither (1) nor (2) would do what you wanted to achieve. The formula that should return the desired result was

Code: Select all

{=COUNTIF(NOT(ISFORMULA(L714:L717));TRUE())}  or  =COUNTIF(NOT(ISFORMULA(L714:L717));"="&TRUE())}
but will not do so because of the bug I mentioned.
Richarda44 wrote:I find criteria tricky because I can never remember when to use "quotes".
This is simple. As I already told, the 'Criterion' parameter always evaluates to a text. As with any text formula doublequotes are needed to mark the constant parts of that text. The & is the operator for concatenation, and there may also occur expressions (subformulae) which return a text result or whose numeric result may be converted automatically into text ("1" in case of TRUE()).

A question I still did not get answered is, for what reason you tried to count the formulae (or the non-formulae) in a range. As you said you wanted to know how many values (numbers) you actually had typed in. Do you make the input into cells that contained formulae in advance? If so: Why?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: Functions in criteria expressions ?

Post by soby »

hey Richarda44

sorry about the pdf, i will attach the file so you can study the formulae

soby
Attachments
juli december.ods
(54.22 KiB) Downloaded 79 times
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Re: Functions in criteria expressions ?

Post by Richarda44 »

Thank you both

@Lup asked "A question I still did not get answered is, for what reason you tried to count the formulae (or the non-formulae) in a range. As you said you wanted to know how many values (numbers) you actually had typed in. Do you make the input into cells that contained formulae in advance? If so: Why?"

The formula I type over is =IF(OR(L717="";L717<1000);"";IF(L717-ROUNDDOWN(L717;-2)>=99-INT(AVERAGE(S715:S717));INT(L717/100)+1;INT(L717/100)))
L717 is the cell above which has a typed 6 digit number and the formula produces the first 3 digits of the next number so I only have to remember the last 3 digits. It also evaluates the average last 3 entries and rounds up the third digit if necessary. So yes I type over the 3 digits made by the formula.

The attached file has the formulae in col L and M of the first sheet

Thanks for the array of pivotal help. Maybe this winter in northern Scotland when snowed up, I will spend time playing with "them". Like a new toy after Christmas :)

Electricity upld.ods[/attachment]
Attachments
Electricity upld.ods
Electricity records file
(75.17 KiB) Downloaded 73 times
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Functions in criteria expressions ?

Post by Villeroy »

Your spreadsheet is incomprehensible to me. How do you get the raw data into the sheets and where do they come from?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Re: Functions in criteria expressions ?

Post by Richarda44 »

Every day I enter numbers into a row (A-M & P) of the sheet 'Electricity & temp record, one row for a date. Data comes from various max-min thermometers and two electricity supply meters, and Column 'N' has a time entered from a clock on the cheap variable time supply. Cols O & R-Z on the same row processes the data giving daily costs. These constants and formulae go down to cover a year's data but I erased most so the file was below 100KB for uploading here.

Main reason to start this sheet was to check on the electricity supply company who would not tell me (even in retrospect) how many hours of cheap rate they supplied. It is a special THTC tariff where they switch it on & off to suit supply demands. They said it varied from 5 to 12 hours depending on weather. The chart sheet has shown me the relationship between the hours they allow and minimum temperatures. And the daily total cost has given me confidence I can afford to heat my home with electricity. The Inputs-Units sheet shows me the relative cost of various store heater settings. The Bills sheet checks my data agrees with their invoice - I consider it a miracle if they agree to within a penny or two!

I know you could do a much better job with a database but, like arrays of pivot tables, databases are too complicated to spend time on (unless it is the old Symphony one).

Thanks for your patience and help - much appreciated.
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
Post Reply