EXCEL FREQUENCY FUNCTION

%3CLINGO-SUB%20id%3D%22lingo-sub-1791482%22%20slang%3D%22en-US%22%3EEXCEL%20FREQUENCY%20FUNCTION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791482%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20calculate%20the%20frequency%20of%20the%20first%20digits%20of%20a%20set%20of%20normally%20distributed%20numbers.%26nbsp%3B%20I%20generated%20a%20random%20set%20of%20number%20that%20are%20normally%20distributed%20by%20which%20I%20then%20generated%20the%20first%20digits%20in%20a%20second%20set%20of%20numbers%20using%20the%20LEFT(data%2C1)%20Function.%26nbsp%3B%20This%20gave%20me%20the%20first%20digits%20of%20the%20numbers%20in%20the%20first%20set.%26nbsp%3B%20I%20then%20used%20the%20set%20of%20first%20digits%20as%20the%20data_array%20in%20the%20Frequency%20function%20and%20I%20used%201-9%20as%20my%20Bins.%26nbsp%3B%20%26nbsp%3BI%20get%20all%20zeros.%26nbsp%3B%20If%20I%20copy%20the%20first%20digit%20set%20and%20past%20special%20in%20to%20adjacent%26nbsp%3Bcells%20and%20convert%20the%20cell%20text%20values%20to%20numbers%20and%20use%20this%20as%20the%20data_array%2C%20the%20Frequency%20function%20works%20and%20gives%20correctly%20the%20frequency%20of%20the%26nbsp%3B%20first%20digits%20in%20to%20their%20respective%20Bins.%26nbsp%3B%20Essentially%2C%20I%20cannot%20get%20the%20Frequency%20function%20to%20work%20if%20the%20data_array%20is%20generated%20by%20a%20formula.%26nbsp%3B%20What%20am%20I%20doing%20wrong%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1791482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791564%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20FREQUENCY%20FUNCTION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835711%22%20target%3D%22_blank%22%3E%40WLMOSTIA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realized%20late%20last%20night%20that%20the%20LEFT%20function%20might%20be%20returning%20a%20text%20number%20due%20to%20having%20to%20converting%20it%20to%20a%20number%20when%20I%20pasted%20special.%26nbsp%3B%20I%20changed%20the%20field%20format%20from%20General%20to%20a%20Number%20so%20there%20is%20definitely%20numbers%20in%20the%20array%20fields%20and%20it%20still%20did%20not%20make%20a%20difference.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to calculate the frequency of the first digits of a set of normally distributed numbers.  I generated a random set of number that are normally distributed by which I then generated the first digits in a second set of numbers using the LEFT(data,1) Function.  This gave me the first digits of the numbers in the first set.  I then used the set of first digits as the data_array in the Frequency function and I used 1-9 as my Bins.   I get all zeros.  If I copy the first digit set and past special in to adjacent cells and convert the cell text values to numbers and use this as the data_array, the Frequency function works and gives correctly the frequency of the  first digits in to their respective Bins.  Essentially, I cannot get the Frequency function to work if the data_array is generated by a formula.  What am I doing wrong?  

2 Replies

@WLMOSTIA 

I realized late last night that the LEFT function might be returning a text number due to having to converting it to a number when I pasted special.  I changed the field format from General to a Number so there is definitely numbers in the array fields and it still did not make a difference.  

@WLMOSTIA 

I fixed the problem converting the LEFT(D1,1) to a number e.g.

     =NUMBERVALUE(LEFT(D1,1)

It appears the the LEFT(D1,1) had a format that the Frequency function did not recognize even though the field was formatted as a number and the field appear to be a number, e.g. =LEFT(D1,1)+1 gives D1 + 1.

 

Thanks to anyone who looked at my problem.

 

Bill Mostia