Can I create a joint frequency chart in Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danielle - 17 Nov 2007 03:36 GMT

I am working with a meteorology professor. If you are not a weather
person, please don't stop reading! The data he's collected is
analogous to all sort of other data I'm sure. Here is a sample:

Wind Speed Solar Radiation
4.535378    12.56788
23.988594    112.34744
4.083793    12.89854
3.983712    214.00074
4.323011    13.2292
23.61268    14.00074

He would like the output to display in as below - in other words, how
many times do a wind speed of 0-3 knots correspond with solar
radiation of 0-100 watts?

He seems to think this can be done using an pivot table but I'm really
new at and am, well stuck.

Is it possible to make this analysis in Excel? If so, can you point me
to a useful post or reference book?

TABLE 7    JOINT FREQUENCY OF OCCURRENCE OF WIND SPEED AND SOLAR
ENERGY IN PERCENT OF TIME

                Solar Energy, Watts per square meter
    0-100 100-200 200-300 Total

Knots (MPH)
0-3 (0-3)            1    1
4-6 (4-7)    3            3
7-10 (8-12)

> 10 1 1 2

TOTAL 4 1 1 6
Thanks in advance -
Danielle

Reply to this Message

Bob Phillips - 17 Nov 2007 13:21 GMT

You probably need a helper column of the wind groups, using a formula like
this

=LOOKUP(A2,{0,4,8;"0-3","4-7","8-12"})

and then pivot this new column and a count of wind speeds.

See http://www.contextures.com/xlPivot01.html
and http://www.peltiertech.com/Excel/Pivots/pivottables.htm

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I am working with a meteorology professor. If you are not a weather
> person, please don't stop reading! The data he's collected is
[quoted text clipped - 34 lines]
> Thanks in advance -
> Danielle

Reply to this Message

Danielle - 20 Nov 2007 01:28 GMT

Bill -

Thanks so much! I created lookups for both parameters and this seems
to have put me on the right track.

Happy Monday :-)
Danielle

Reply to this Message

Bob Phillips - 20 Nov 2007 12:31 GMT

Uh? It's Tuesday <bg>

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Bill -
>
[quoted text clipped - 3 lines]
> Happy Monday :-)
> Danielle

Reply to this Message