How do you do a frequency distribution in excel using bins that have both an upper and lower limit? I know how to do it if the bins are just a single number, but not a range. For example: Data // Bins ----------------- 1 // 1-U-2 2 // 2-U-3 3 // 3-U-4 4 // 4-U-5 5 // 5-U-6 I know how to do the formula in excel if the Bins column was just 1,2,3,4,5, but not with the upper and lower limits. Can somebody help me out?
use COUNTIF
Thanks.
I can’t seem to get the COUNTIF function to work correctly… It needs to count all values that are, for example, greater than 0 BUT less than 2 (NOT greater than 0 AND less than 2) I do not know how to get the formula right to recognize that it must meet both criteria. Can you give me a formula that works for this case? =COUNTIF(E5:E1683,"???") --> Need to fill in the ???
So you want all values between 0 and 2, i.e., greater than 0 but less than 2 =COUNTIF(B5:E1683,">0")-COUNTIF(E5:E1683,">2") This calculates all the values greater than 0 and subtracts from it all of the values greater than 2 leaving what’s in between.
If you put the lower limit of your bins in column A, and the upper limit in column B, you can do in Column C: =sum(if($E$5:$E$1683>=A1,if($B$5:$E$1683