Need FREQUENCY function to round down numbers

[benabean]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a bin array set up as 0,0.5,1,1.5 ...etc and the corresponding results calculated from the FREQUENCY function.

In my data array, I have values like 1.18,2.05,3.12.
The FREQUENCY function rounds these to 1.5,2.5,3.5 respectively and puts them into the designated bins.
What I would like is if a value falls with (x-0.25) < x < (x+0.25), to round that number to x.
Taking the 1.18 from above, this would that it rounds to 1. If I had 1.26 say, this would round to 1.5.

Any help is greatly appreciated
regards, b.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a bin array set up as 0,0.5,1,1.5 ...etc and the corresponding results
calculated from the FREQUENCY function.

In my data array, I have values like 1.18,2.05,3.12.
The FREQUENCY function rounds these to 1.5,2.5,3.5 respectively and puts them
into the designated bins.
What I would like is if a value falls with (x-0.25) < x < (x+0.25), to round
that number to x.
Taking the 1.18 from above, this would that it rounds to 1. If I had 1.26
say, this would round to 1.5.

Any help is greatly appreciated
regards, b.

FREQUENCY() doesn't really round. instead, the count in each bin is
increased for every data array value less than or equal to the bin value.

The easiest way would be to adjust your bins, e.g.:

=FREQUENCY(A1:A15, F1:F6 + 0.25000001)

(Note that you didn't specify what should happen with, say 1.25 - the
above assumes it should be in the 1 bin, not the 1.25 bin).

You can also add or subtract a constant to your data array. Note that
this will count blanks, if they exist in the array:

=FREQUENCY(A1:A15 - 0.25, F1:F6)

 

[r_poetic]

FREQUENCY() doesn't really round. instead, the count in each bin is
increased for every data array value less than or equal to the bin value.

The easiest way would be to adjust your bins, e.g.:

   =FREQUENCY(A1:A15, F1:F6 + 0.25000001)

(Note that you didn't specify what should happen with, say 1.25 - the
above assumes it should be in the 1 bin, not the 1.25 bin).

You can also add or subtract a constant to your data array. Note that
this will count blanks, if they exist in the array:

   =FREQUENCY(A1:A15 - 0.25, F1:F6)- Hide quoted text -

- Show quoted text -

Thanks for the informative tips. I hadn't previously seen a number
added to a range like this, and it seems like a useful trick.

Although adding a number to a range in a formula works as advertised
for Frequency(), it doesn't seem to work for Sum() or other formulae.
For instance,

if A1:A4 is {1,2,3,4}, then SUM(A1:A4+1) doesn't yield 14.

Where can we learn more about adding a number to a range?

 

[benabean]

FREQUENCY() doesn't really round. instead, the count in each bin is

increased for every data array value less than or equal to the bin value.

The easiest way would be to adjust your bins, e.g.:

=FREQUENCY(A1:A15, F1:F6 + 0.25000001)

(Note that you didn't specify what should happen with, say 1.25 - the
above assumes it should be in the 1 bin, not the 1.25 bin).

You can also add or subtract a constant to your data array. Note that
this will count blanks, if they exist in the array:

=FREQUENCY(A1:A15 - 0.25, F1:F6)

Thanks for your help J.E.
I have tried your second suggestion, adding the constant and the data seems to do what I want. Only problem is now the x axis scale is not correct; before I made any adjustments the scale was doing what I wanted: using my column titled 'bin width' as the scale, going from 0 to 6.5 in 0.5 increments.
Now it seems to ignore that and just number from 0 to 14.
Any suggestions?

Thanks for your help, b.

 

[Bob Greenblatt]

Thanks for the informative tips. I hadn't previously seen a number
added to a range like this, and it seems like a useful trick.

Although adding a number to a range in a formula works as advertised
for Frequency(), it doesn't seem to work for Sum() or other formulae.
For instance,

if A1:A4 is {1,2,3,4}, then SUM(A1:A4+1) doesn't yield 14.

Where can we learn more about adding a number to a range?

=Sum(a1:a4+1) correctly results in 2. The reason is that as a normal
formula, only the first array value (and the operation) is returned to the
cell. Thus this results in 1+1. If you array enter this formula by pressing
control-shift-enter, the result will indeed be 14. (Excel brackets array
formulas with braces {} to show that it is an array formula.)

You can learn more about array formulas and arrays fro Excel's Help, or any
of the commercial books about Excel. Just check the index entries and some
sample chapters to see how arrays are treated before buying.

 

[JE McGimpsey]

if A1:A4 is {1,2,3,4}, then SUM(A1:A4+1) doesn't yield 14.

It does if the formula is array-entered.

If you're using XL04, see the "About array formulas and array
constants" topic in XL Help.

If you're using XL08, see

http://cpearson.com/excel/ArrayFormulas.aspx

(since the topic doesn't show up in XL08 help).

 

[JE McGimpsey]

Thanks for your help J.E.
I have tried your second suggestion, adding the constant and the data seems
to do what I want. Only problem is now the x axis scale is not correct;
before I made any adjustments the scale was doing what I wanted: using my
column titled 'bin width' as the scale, going from 0 to 6.5 in 0.5
increments.
Now it seems to ignore that and just number from 0 to 14.
Any suggestions?

Hmm... you didn't mention anything about an "x axis scale" in your
original post.

It probably would be easiest to use a helper column. Use the original
column for your chart, use a column with the rounded values for the
Frequency calculation, e.g.:

B1: =ROUND(A1*2,0)/2

 

[benabean]

FREQUENCY() doesn't really round. instead, the count in each bin is

increased for every data array value less than or equal to the bin value.

The easiest way would be to adjust your bins, e.g.:

=FREQUENCY(A1:A15, F1:F6 + 0.25000001)

(Note that you didn't specify what should happen with, say 1.25 - the
above assumes it should be in the 1 bin, not the 1.25 bin).

You can also add or subtract a constant to your data array. Note that
this will count blanks, if they exist in the array:

=FREQUENCY(A1:A15 - 0.25, F1:F6)

Sorry get way ahead of myself talking about graphs.

Basically I am trying to plot a histogram that shows the number of electrons on oil droplets.

Theoretically my data should be integer numbers of electrons, but experimentally this is often difficult to achieve, thus my data has values like 1.18 electrons, 3.23 electrons.
As I take more measurements I should see concentrations of data around 1,2,3... marks ie- 1,2,3...electrons. In the limit where the number of measurements taken goes to infinity, my data would resemble delta functions at 1,2,3...etc.

I know there is no HISTOGRAM function available in excel(?), so I have got around this by using the FREQUENCY function. I have bins set up as 0,0.5,1.0,1.5... and I have added a constant to the data, -0.25, (like you suggested above).
When I tried it with my data it worked perfectly. Now I have since taken more measurements and added them to the data set. Now I do have values such as 1.25, 2.25, but these are placed in the wrong bins, namely 1, 2, not in bins 1.5, 2.5 as I would have liked.
Is there anyway I can make this happen without completely changing my functions?

Many thanks for your help thus far, I've learnt alot from your input!
b.

 

[Carl Witthoft]

First of all, there is a histogram function in the addin, but that
won't exist if you have Excel 2008.

What to do? Try my spiffy histogram generator, based on the
original spreadsheet from the OATBRAN project

http://home.comcast.net/~cgwcgw/histogram.zip

But you should also be careful: you don't want to presuppose the answer
(that electron charge is quantized) in your graphics. So, ignoring Excel
vs histograms for the moment, I would recommend a 'grass plot' -- which
Excel doesn't suppport, but you can fake it by assigning each datum a
'fake' y-coordinate value that is taken from rand().
Make column A your set of observations (number of electrons for a given
drop), and column B the randoms.

Then what you'll get is each electron count (integer or not) plotted
along the x-axis, with the y-values used solely to spread out common
values vertically for your visual pleasure. The "density" of dots --
scatterplot as points only, in case that wasn't obvious -- will suggest
the central values.

The advantage of this grassplot (or similar tricks) over a histogram is
that it doesn't presuppose the binsize.

BTW, if you want to get more accurate, you'll need to plot a small
vertical line for each datum, and should two tests yield exactly the
same value (e.g. 2.18100000000 for both), make the line twice as
high. The methodology is left to the reader .

FREQUENCY() doesn't really round. instead, the count in each bin is
increased for every data array value less than or equal to the bin value.

The easiest way would be to adjust your bins, e.g.:

=FREQUENCY(A1:A15, F1:F6 + 0.25000001)

(Note that you didn't specify what should happen with, say 1.25 - the
above assumes it should be in the 1 bin, not the 1.25 bin).

You can also add or subtract a constant to your data array. Note that
this will count blanks, if they exist in the array:

=FREQUENCY(A1:A15 - 0.25, F1:F6)

Sorry get way ahead of myself talking about graphs.

Basically I am trying to plot a histogram that shows the number of electrons
on oil droplets.

Theoretically my data should be integer numbers of electrons, but
experimentally this is often difficult to achieve, thus my data has values
like 1.18 electrons, 3.23 electrons.
As I take more measurements I should see concentrations of data around
1,2,3... marks ie- 1,2,3...electrons. In the limit where the number of
measurements taken goes to infinity, my data would resemble delta functions
at 1,2,3...etc.

I know there is no HISTOGRAM function available in excel(?), so I have got
around this by using the FREQUENCY function. I have bins set up as
0,0.5,1.0,1.5... and I have added a constant to the data, -0.25, (like you
suggested above).
When I tried it with my data it worked perfectly. Now I have since taken more
measurements and added them to the data set. Now I do have values such as
1.25, 2.25, but these are placed in the wrong bins, namely 1, 2, not in bins
1.5, 2.5 as I would have liked.
Is there anyway I can make this happen without completely changing my
functions?

Many thanks for your help thus far, I've learnt alot from your input!
b.[/QUOTE]