Hi,
I have searched far and wide for a solution to this and spent hours trying to figure it out, and I am now admiting defeat and hoping someone will be able to help me.
I have the following type of data:
Example dataset:
<tbody>
</tbody>
The real data is more substantial (over 10,000 cells worth), this is just to illustrate.
I have generated the frequencies using the following formula "=COUNTIF(A:A,"<"&C3)-COUNTIF(A:A,"<"&C2)". This has worked fine and I can generate a histogram from this.
The problem is that in addition to this, I would like to look not just at the frequency of the level appearing, but to generate a histogram of time spent at that level. I.E. I am trying to come up with a formula that looks at the column with the levels (column A) if the value in column A falls in the correct range, then the corresponding value in column B would be recorded and summed.
Not sure if I explained that too well. For this small data set, I can of course fill in the values manually, and they would come out as shown below:
Example manually calculated solution:
<tbody>
</tbody>
Thank you in advance for any suggestions to do this! It's driving me crazy!
I have searched far and wide for a solution to this and spent hours trying to figure it out, and I am now admiting defeat and hoping someone will be able to help me.
I have the following type of data:
Example dataset:
A | B | C | D | E | |
1 | Level | Time | Bin Range | Level Frequency | Level Frequency * Time |
2 | 1.2 | 40 | 0 | 2 | |
3 | 3.5 | 100 | 1 | 0 | |
4 | 3.9 | 30 | 2 | 0 | |
5 | 2.8 | 130 | 3 | 3 | |
6 | 1.6 | 50 | 4 | 1 | |
7 | 6.2 | 80 | 5 | 0 | |
8 | 3.4 | 10 | 6 | 1 | |
9 | 4.7 | 240 | 7 | 0 |
<tbody>
</tbody>
The real data is more substantial (over 10,000 cells worth), this is just to illustrate.
I have generated the frequencies using the following formula "=COUNTIF(A:A,"<"&C3)-COUNTIF(A:A,"<"&C2)". This has worked fine and I can generate a histogram from this.
The problem is that in addition to this, I would like to look not just at the frequency of the level appearing, but to generate a histogram of time spent at that level. I.E. I am trying to come up with a formula that looks at the column with the levels (column A) if the value in column A falls in the correct range, then the corresponding value in column B would be recorded and summed.
Not sure if I explained that too well. For this small data set, I can of course fill in the values manually, and they would come out as shown below:
Example manually calculated solution:
A | B | C | D | E | |
1 | Level | Time | Bin Range | Level Frequency | Level Frequency * Time |
2 | 1.2 | 40 | 0 | 2 | 90 |
3 | 3.5 | 100 | 1 | 0 | 40 |
4 | 3.9 | 30 | 2 | 0 | 0 |
5 | 2.8 | 130 | 3 | 3 | 140 |
6 | 1.6 | 50 | 4 | 1 | 240 |
7 | 6.2 | 80 | 5 | 0 | 0 |
8 | 3.4 | 10 | 6 | 1 | 80 |
9 | 4.7 | 240 | 7 | 0 | 0 |
<tbody>
</tbody>
Thank you in advance for any suggestions to do this! It's driving me crazy!