Hi,
I've been battling with this for some weeks now and everytime I seem to have solved it, I find it comes back x weeks later. Scenario:
I have a list of 8760 values (the number of hours in a year) which vary from around -9 to +32. However these values are dependent on other things in the workbook (namely a dropdown which causes this list of numbers to change respectively), so when trying to create a Histogram-type table, with frequency of each value, I cannot use the Excel 2013 Analysis Toolkit Add-in and just hit 'Histogram', as it creates a static, non-automated table.
The lowest and highest possible values are -15 and +40 respectively. I have a column of integers, from -15 to 40 (ie, 56 rows) and, in the next column, the following formula:
Where Column G is where the 8760 data points are and U is where the set of integers are. This should be counting the number of values between (in this case) -15 and -16. This is then autofilled down for the rest of the column, resulting in 56 frequency values.
However, as a check, I put a
at the bottom of the column of frequencies and it adds up to 7846 instead of the expected 8760. I have checked each individual countifs formula and have a =count() function at the bottom of the G column, so I know that 8760 data points exist.
I did this all before Christmas and I'm sure it then showed 8760 in the countifs column. But after ~2 weeks off, it seems to not want to work right anymore!
[N.B. In a formula nearby that is exactly the same in style and use but for a different set of 8760 values, it works fine and has a sum of 8760. I have copied and pasted this formula and changed the various references but it still comes out with the same answers. Very confusing and frustrating!!]
I've been battling with this for some weeks now and everytime I seem to have solved it, I find it comes back x weeks later. Scenario:
I have a list of 8760 values (the number of hours in a year) which vary from around -9 to +32. However these values are dependent on other things in the workbook (namely a dropdown which causes this list of numbers to change respectively), so when trying to create a Histogram-type table, with frequency of each value, I cannot use the Excel 2013 Analysis Toolkit Add-in and just hit 'Histogram', as it creates a static, non-automated table.
The lowest and highest possible values are -15 and +40 respectively. I have a column of integers, from -15 to 40 (ie, 56 rows) and, in the next column, the following formula:
Code:
=COUNTIFS('Edited data'!$G$9:$G$8768,">=" &U9, 'Edited data'!$G$9:$G$8768,"<" &U10)Where Column G is where the 8760 data points are and U is where the set of integers are. This should be counting the number of values between (in this case) -15 and -16. This is then autofilled down for the rest of the column, resulting in 56 frequency values.
However, as a check, I put a
Code:
=sum(V9:V64)I did this all before Christmas and I'm sure it then showed 8760 in the countifs column. But after ~2 weeks off, it seems to not want to work right anymore!
[N.B. In a formula nearby that is exactly the same in style and use but for a different set of 8760 values, it works fine and has a sum of 8760. I have copied and pasted this formula and changed the various references but it still comes out with the same answers. Very confusing and frustrating!!]
