Counting number of cells w. values higher than X

English
#1

Hello,

Not sure if this is the correct category to ask, but I’m not sure what tool/formula exactly I am looking for.

The challenge:
1st row = dates where a worker was on duty.
2nd row = anmount of hours spent on each duty.
I like to count the number of dates where a worker spent at least 6hrs on duty.
So using “MAX” would not work.
For just calculating the pure number of duty dates, I count entries against the same number of blanks =ROWS($C1:$C26)-COUNTBLANK(B1:B26).
But =ROWS($C1:$C26)-COUNTBLANK(B1:B26)>6 returns “TRUE” or the number 1 (which is not correct).
Am I on the wrong track?

Another question:
Is there a comprehensive summary of all formulas, just as a three column list with functions explained and an example (of the syntax)- so one could browse through it and solve problems…?

#2

The function COUNTIF is your friend!
=COUNTIF(B2:T2,">=6")

1 Like
#3

If there is more than one worker that’s a bad design.

  • Generally. Ona data set per row allows for reasonable evaluation and usage of special tools (like filtering).
  • Generally again: Attempts to describe what’s intended to achieve with a mix of words and dislocated formulas often fail. At least somebody trying to help will see unnecessary dificulties.
  • Therefore: Attach an example file.

General rule here: One Question per thread.

Afaik: No. And imo such a thing is impossible.
Just think of the many functions with amny parameters and many use-cases…

#4

Thanks.
This worked!

#5

Well, each woker has a different tab, so that’s not a problem.
It was COUNTIF that solved the challenge.
And yes, an at-a-glance list of formulas might be another challenge I might give in to over the coming month (or so) if it doesn’t exist yet.
I added this question because it may have helped me find the solution myself (taking off some load here).
So I might start collecting formulas then…

#6

It require an “every.other-row-design”, afaik.
This includes that you can’t evaluate columns without a lot of complications.

#7

A collection of all functions is in https://opengrok.libreoffice.org/xref/core/sc/inc/scfuncs.hrc. The first line of a function is the text that you see in Calc in the description of the function in the sidebar. Further lines contain the descriptions for the parameter.

The relationship between the internal names in that file and the UI names is in https://opengrok.libreoffice.org/xref/core/formula/inc/core_resource.hrc

The list in the Wiki is maintained manually and might not contain the newest functions that will be contained in version 25.8. Documentation/Calc Functions/List of Functions - The Document Foundation Wiki

It would be good to have a script, that extracts the information from the files in core.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.