Ask Your Question
0

COUNTIF criteria evaluation - function applied on range cells? [closed]

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-20 21:39:18.633719

1 Answer

Sort by » oldest newest most voted
1

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

Question Tools

1 follower

Stats

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

Seen: 219 times

Last updated: Jan 11 '17