Forum Discussion

WLMOSTIA's avatar
WLMOSTIA
Copper Contributor
Oct 17, 2020

EXCEL FREQUENCY FUNCTION

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?  

office 365
  • WLMOSTIA's avatar
    WLMOSTIA
    Copper Contributor
    Oct 17, 2020

    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

  • WLMOSTIA's avatar
    WLMOSTIA
    Copper Contributor
    Oct 17, 2020

    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.  

Resources