Ask Your Question
0

COUNTIF criteria evaluation - function applied on range cells?

asked 2017-01-11 15:31:36 +0200

Michal Pták gravatar image

My friend asked me for help and we only found a solution we don't like 100%.

Problem: I have a table with angle values in degrees (0, 10, 20, ..., 180) - that is range A2:A20. I want to use COUNTIF to tell me, how many of these have SIN > 0,95.

My solution: In column B I use RADIANS to convert degrees to radians. In column C I calculate the SIN value. Then I can write COUNTIF(C2:C20;">0,95"). I don't like this solution because I had to add two extra columns that hold values I don't need to see, I only have them so that COUNTIF can tell me what I need.

image description


Anything like this? I was hoping for something like this:

  • COUNTIF(SIN(RADIANS(A2:A20)); "> 0,95") or better
  • COUNTIF(A2:A20; "SIN(RADIANS(value))>0,95")

The "value" would be the value of the evaluated cell. In ">0,95" you can feel this keyword... "value > 0,95"... but not having it means not being able to apply functions to it.

Is there a better solution to my problem?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-01-11 16:05:05 +0200

m.a.riosv gravatar image

=SUMPRODUCT(SIN(A2:A20)>0.95)

edit flag offensive delete link more

Comments

I admit I am not used to working with matrices. And "matrices" are key ingredient of the solution. I will learn about matrices in Calc more. Thanks for help! :)

Michal Pták gravatar imageMichal Pták ( 2017-01-11 19:01:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-01-11 15:31:36 +0200

Seen: 142 times

Last updated: Jan 11 '17