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?
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