Right I've got a simple problem I appear to be unable to solve as I'm stupid this morning. List of numbers
3.2
1.4
3.4
2.5
3.5
etc.. about 100 of them between one and five.
I want to create a really simple chart showing the number of each values within ranges so basically
1-1.5 - 10
1.5-2.0 - 15
etc
all the way to five
I'm sure I need to create an expression using =AND to validate if a number is between the two values and then a countif if true. But I can't get it to work. Fairly urgent, all help appreciated!
I got close with =COUNTIF(cellrange, "<1") which works except I can't get multiple arguments so >1 and < 1.5
DCOUNTA
or
FREQUENCY
should help
[url= http://www.excel-easy.com/examples/histogram.html ]something like this?[/url]
cell B1: =COUNTIF(A1:A100,"=<1.5")
cell B2: =(COUNTIF(A1:A100,"=<2"))-B1
cell B3: =(COUNTIF(A1:A100,"=<2.5"))-B2
etc...
+countifs(cellrange,">"&1,cellrange,"<"&1.5)
Replace 1 and 1.5 with the numbers you want for each range. Speech marks are part of the formula.
Is this of any use...
=COUNTIFS(range,">1",range,"<1.59")
I've tried a few variations out and it works for me.
countifs works wonders
for more advancedm i think the FREEQUENCY function may also do the job well
That's it. Thanks guys. Brilliant. I never knew about COUNTIFS. A virtual beer to you all 🙂
If it makes it easier you can replace the numbers in the formula with cell references, and can then just change those values accordingly.
There's also Sumifs, Averageifs etc, all quite useful.
Yeah did that subsequently. All good 🙂