Help with relative frequency percentages

Hi everyone.

I've decided to ask for some help after pulling my hair out for a couple of days :/

I have temperature data listed in column A, and a corresponding sample figure taken at that temperature in column B next door. I need to find a way to sum each sample amount at regular intervals of temperature (i.e. in 0.1 increments). The pivot table only gives increments around temperatures where positive sample numbers occur (i.e. where there is 0 sample, say at 14.2 degrees), the group field will look like this (14 - 14.1; 14.3-14.4... etc), missing the 14.2 interval completely. For the analysis I need to run, I need all temperature group intervals, not just the ones that include positive sample figures.

Can I force the pivot table to include all 0.1 increments? or is there a function I don't k ow about that might help me achieve this?

Thanks in advance

Stu

If I understand your need correctly you could manually create a table, similar to a pivot table, and then use formulas to populate it. SUMIFS will be your friend on this one.

Without some kind of sample data it's difficult to give you more of a solution than that, but post some data here and I'll show you what I mean.

BSB.

Hi BSB - thanks ever so much for your reply!

Yes, I think you might be on the same page as me. The data posted here is a redacted version of my current worksheet. In columns A & B are the data for which I need frequencies / percentages. These Relative percentages will be used to create a quotient analysis later on, but to the issue at hand:

Column F are the intervals of temperature (0.1 degree increments) for which I'd like to display (a) the sum of eggs/ cubic meter at each interval, and (b) the percentage value of said sum for the entire number of eggs. The pivot table in column P is a demonstration of what I need, however, you can see that temperature intervals are missing (i.e., the first row is 12.3-12.1, and then jumps to 14-14.1 - missing the values in-between). This is due to a lack of data points representing those temperatures - however for the quotient to be an accurate representation, it needs to match the spread of temperature frequency intervals as demonstrated in columns F, G and H.

The resulting table I hope to achieve would take on the format as displayed in columns K, L, M (highlighted) - with matching temperature intervals.

Hope this makes some sense. I'm so very out of practice with excel (my undergrad training was quite some time ago!)

Cheers

Stu

Book1.xlsx

Have a look at columns L & M in the attached. Is this anything near what you're after??

Well, thats just marvellous!

You Sir (Im assuming the gender), are what we'd call here in Oz a bloody legend! This is exactly the formula I needed. Thank you very much. The SUMIFS function is one I'm not familiar with. I can use this in many applications in the future.

When I'm next in london a beer or two are on me.

Consider this one solved.

Thanks again

Stu

No problem mate. Happy to help.
Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

I shall look forward to the beer

BSB.