Histograms

[meperezs]

Hello,

I am attempting to do histograms in excel 2008. I know how to do it in the windows counterpart because of school, but I have homework to accomplish for the class and cant find the function anywhere in the mac side of business.

 

[CyberTaz]

Sorry, but I'm afraid you've hit one of the sore spots of Excel 2008. The
Histogram feature was a part of the Analysis ToolPak add-ins which are no
longer included due to the lack of support for VBA in this version. Unless a
future update brings back a suitable alternative or something comparable is
developed by a 3rd party I'm afraid you'll need to use a previous version
for that capability - as well as some others.

The only other alternative is to run a Windows version of Excel on an Intel
Mac in Parallels/Fusion/Boot Camp or VPC7 if a PPC Mac. [I don't know if
comparable/compatible features are available in other worksheet programs or
not.]

 

[Mike Middleton]

meperezs -

Mac Excel 2008 does not have the Data Analysis features of the Analysis
ToolPak, so there is no Histogram option.

As a workaround, you can prepare a frequency distribution using the
FREQUENCY array-entered worksheet function (or COUNTIF or a Pivot Table),
and you can plot using the Column chart type.

Another source is Jon Peltier's web site
http://www.peltiertech.com/Excel/Charts/statscharts.html#Hist1

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



Hello,

I am attempting to do histograms in excel 2008. I know how to do it in the
windows counterpart because of school, but I have homework to accomplish for
the class and cant find the function anywhere in the mac side of business.

 

[Carl Witthoft]

I noticed this request has come up a couple times. FWIW, here is a Q&D
bit of MatLab code (actually I run it under FreeMat) to dump data into a
histogram. I'm sure there's a tighter algorithm but this works-- you
may get a few 'lost' points at the top end depending on how your machine
and your Matlab clone work.

% simple histogram binner "hist.m"
% take any input array and bin it up. For first cut, just spec number
of
% bins and use min, max to set boundaries.
%
function [y,binvals] = hist(array,nbins);
%
%
%
if (nargin ~=2)

error('Usage: hist(array,nbins')
end
% get endpoints; damn the roundoffs, full speed ahead
low = min(array)-0.001;
high = max(array)+ 0.001 ;
delta = (high - low)/nbins;
j = 1;
while (low < high) %getting cute here- reusing the variable
% bracketed stuff returns logical 1 or 0 for true/false.
bin(j) = sum(array >= low & array < (low + delta));
binval(j) = low;
j = j + 1;
low = low + delta;
end
% need this only because I didn't use "y" and "binvals" inside loop
y = bin;
binvals = binval;

% there may be leftover values (at least one, the way I calc'd "high" ),
so
% y(j) = numel(array)-sum(bin);
%this leaves y longer than binvals. Tough noogies
% not clear to me (yet) why this isn't necessary. My last bin, i.e. low
+k*delta,
% somehow always manages to be slightly greater than max(array) . It is
a cumulative
% rounding error -- I have no idea whether it will always go "large"

 

[JE McGimpsey]

Sorry, but I'm afraid you've hit one of the sore spots of Excel 2008. The
Histogram feature was a part of the Analysis ToolPak add-ins which are no
longer included due to the lack of support for VBA in this version. Unless a
future update brings back a suitable alternative or something comparable is
developed by a 3rd party I'm afraid you'll need to use a previous version
for that capability - as well as some others.

The only other alternative is to run a Windows version of Excel on an Intel
Mac in Parallels/Fusion/Boot Camp or VPC7 if a PPC Mac.

Oh baloney.

Check out the Histogram workbook here:

http://www.coventry.ac.uk/ec/~nhunt/oatbran/

About all that of the ATP Wizards did is help organize input and output
of built-in XL functions and charts (except for the Random Number
Generator, which was, and still is, execrable).

Granted, the output of the ATP is organized, and it does give people who
know what they're doing a shortcut (and allows those who don't to come
up with result their instructor wants - sometimes), but the wizards
aren't magic, nor even particularly complex.

Of course, the advantage of doing it directly in a workbook is that you
can see the formulae, and verify that they're operating on the proper
ranges/results, rather than simply hoping the black-box works.



I wouldn't mind if MacBU could somehow manage to bring back add-in
functionality, but the ATP isn't the reason...

 

[CyberTaz]

Oh baloney.

<snip>

Gee John - don't be so touchy;-)

I didn't mean to infer that Histograms couldn't be done in Excel, just that
the convenience of the add-in was not available in 2008 and that there isn't
as yet anything to directly take its place.

Many of those who relied on the add-in may not be able to devise such a
workbook of their own by creating the necessary functions - I *know* I
couldn't, at least not without devoting far more time than I'm able.

The link you provided is exceptionally generous & I'm sure the OP as well as
many others will benefit from it. Thanks for the follow-up!

 

[JE McGimpsey]

Many of those who relied on the add-in may not be able to devise such a
workbook of their own by creating the necessary functions - I *know* I
couldn't, at least not without devoting far more time than I'm able.

TOUCHY!?!? You wanna see TOUCHY!?!?!? At least I said "baloney"!!!
<vbseg>

OK, I'm a little sensitive - but seeing people complaining about "not
being able to do stats" without the ATP is a real peeve.

What you point out is precisely the more-than-potential problem that I
see all the time with my academic clients.

<rant>
While *some* users know enough about statistics to make the ATP a great
shortcut, a *lot* of people use it as a crutch, ignorant of the
underlying functions, and seemingly content to assume that the fact that
the ATP can produce *a* result means that it's a *valid* result.

I recognize how convenient the wizards are.

But, for just one example, I can't tell you how many times I've seen
t-tests used in biological, economic and social science applications
BECAUSE THE ATP MAKES IT EASY, even when it's COMPLETELY inappropriate
with their small non-Gaussian distributions.

If one *can't* produce the same results with functions, then I submit
that they *shouldn't* produce them with the ATP.
</rant>

 

[Mauricio]

J.E., sorry to say so, but your answer displays a bit of high-horsedness (yes, I just made that up). I am perfectly capable of producing the same results with functions as with the ATP. I just don't feel like wasting the time to do it after "upgrading" to a new version. Asking that an upgrade delivered after 4+ years contain at least the same basic capabilities of the application it replaces is not unreasonable. Microsoft dropped the ball on this one; I hope they pick it up soon.

 

[Sebastian Lourido]

I agree with Mauricio. It's very upsetting that features from a previous version have been omitted. This will force me to use a different program. :angry:

 

[Carl Witthoft]

I agree with Mauricio. It's very upsetting that features from a previous
version have been omitted. This will force me to use a different program.
:angry:

relax.... go to Oatbran to get their histogram generator, which does
not use the toolbox or macros.

I have a jazzed-up version which is a little more flexible than theirs,
so email me if you'd like a copy.

Carl