Excel/Math Question..... Yo estoy estupido

[aphex]

Friend posed this problem to me, and for some stupid reason i cant think through it....

He has a range of numbers from 0 to 20... How can we get excel to count only the numbers in the ranges....

0-5
5-10
10-15
15-20

We've been trying COUNTIF but cant nail the formula right 🙂

 

[aphex]

Whoops, i guess this would have been better in Software.....

 

[amol]

no, i wanted to be the "wrong forum nazi" 🙁

 

[jamesave]

sort the number first. that's the easiest way.

 

[Ilmater]

Originally posted by: aphex
Friend posed this problem to me, and for some stupid reason i cant think through it....

He has a range of numbers from 0 to 20... How can we get excel to count only the numbers in the ranges....

0-5
5-10
10-15
15-20

We've been trying COUNTIF but cant nail the formula right 🙂

Nested IF statements would be easiest off the top of my head.

Edit: What you do is, say column A (rows 1 - 50) are your values and column B are your ranges.

In column D, put this: =IF(ROUNDUP(A1,0)<5.01,A1,"")
In column E, put this: =IF(ROUNDUP(A1,0)>5,(IF(ROUNDUP(A1,0)<10.01,A1,""),"")
In column F, put this: =IF(ROUNDUP(A1,0)>10,(IF(ROUNDUP(A1,0)<15.01,A1,""),"")
In column G, put this: =IF(ROUNDUP(A1,0)>15,(IF(ROUNDUP(A1,0)<20.01,A1,""),"")

Then, in cells C1 - C4, put these formulas:

=COUNT(D1😀50)
=COUNT(E1:E50)
=COUNT(F1:F50)
=COUNT(G1:G50)

Edit 2: Thanks to dman for the obvious rounding idea.

Edit 3: Whoops, you said you wanted to COUNT how many values fell in those ranges, not SUM them. Fixed.

Edit 4: I'm an Excel God. I need to find a job that pays to know ridiculous tricks in Excel.

 

[dullard]

Originally posted by: Ilmater
Nested IF statements would be easiest off the top of my head.

 

[Yossarian]

I would use VBA and a case statement.

 

[ggavinmoss]

countif ?

 

[dman]

RoundUp(n/5,0)
and

Countif(1...5)?

Just an option.

 

[Ilmater]

See above. Sorry for the multiple edits, but it's all there.

 

[ActuaryTm]

The following should also work as well:

• COUNTIF(CellRange,">=0")-COUNTIF(CellRange,">5")
  COUNTIF(CellRange,">=5")-COUNTIF(CellRange,">10")
  etc.

At least, according to your segmentations.

 

[MrMaster]

I'm with you. Super good excel and access skills but no computer degree behind it so no job paying me just to do that.

 

[Ilmater]

Originally posted by: ActuaryTm
The following should also work as well:

• COUNTIF(CellRange,">=0")-COUNTIF(CellRange,">5")
  COUNTIF(CellRange,">=5")-COUNTIF(CellRange,">10")
  etc.

At least, according to your segmentations.

Showoff. 😉