Between? Is there a way?

N

NCINDASUN

I'd like to count the number of cells in a range whose value is between two
preset numbers. I would imagine that it would be something like

=countif(between(a1:a10,20,30))

but there doesn't seem to be a "between" function.

Can anyone help? Thanks
 
J

JMB

where minimum and maximum are named cell references

=COUNTIF(A7:A17,">"&Minimum)-COUNTIF(A7:A17,">"&Maximum)
 
H

Harlan Grove

JMB wrote...
where minimum and maximum are named cell references

=COUNTIF(A7:A17,">"&Minimum)-COUNTIF(A7:A17,">"&Maximum)
Click to expand...
....

That includes entries in A7:A17 equal to Maximum but not equal to
Minimum. If the OP wants to include both bounds in the count,

=COUNTIF(Rng,">="&Min)-COUNTIF(Rng,">"&Max)

If the OP wants to exclude both min and max bounds from the count,

=COUNTIF(Rng,">"&Min)-COUNTIF(Rng,">="&Max)

To include items equal to min but exclude items equal to max,

=COUNTIF(Rng,">="&Min)-COUNTIF(Rng,">="&Max)
 
U

ufo_pilot

=IF(A1>A2,"LARGE",IF(A1<A3,"SMALL","OK"))

The numeric version for an overview:
=IF(A1>1000,1,IF(A1<1000,-1,0))
 
H

Harlan Grove

ufo_pilot wrote...
....
The numeric version for an overview:
=IF(A1>1000,1,IF(A1<1000,-1,0))
Click to expand...
....

So clever! But why not

=SIGN(A1-1000)

? Or don't you like short and simple?
 
Top