Can I create a joint frequency chart in Excel?

[Danielle]

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

 

[Bob Phillips]

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


--
HTH

Bob

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

 

[Danielle]

Bill -

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

Happy Monday
Danielle

 

[Bob Phillips]

Uh? It's Tuesday <bg>

--
HTH

Bob

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