Shifting columns using excel formula

Hi, would you please help me with this? I have many datasets, I have posted an example of two othem (A and B) to illustrate the problem I am having. A dataset has 2 columns of data for a histogram. The x-axis: the histogram bin centers and y-axis: the counts per bin. These data are listed starting from row 2. I would like to plot this data points and overlay with a binomial distribution curve. In another column, I calculated the normal binomial distribution using the excel function. The binomial distribution data also starts from row 2. Note that the length of data in the column with the binomial distribution is the same as that of the column with the y-axis data, not including the starting and ending zero-values. However, the zero-values in between the integers are counted. I would like to graph the the histogram with the overlapping binomial distribution curve. To do so, I would need the binomial distribution column to be shifted down in order to match the first integer in the y-axis column. How would I go ahead to do that using excel worksheet formulas? I have been reading about the Offset function, but it is confusing how it would work here. I need the formulas to be repeatable with many of my other data sets which have varying lengths.

You can put this formula in K2:

=IF(ROWS($1:1)< MATCH(0.01,$B$2:$B$10)+1,"",INDEX(F:F,ROWS($1:1)-MATCH(0.01,$B$2:$B$10)+1))

then copy it down. (Actually, I put it in J2, so I could compare directly with your numbers in column K). It looks for the first non-zero cell in the first 10 cells of column B, and then moves the values from column F down by the required number of rows.

Here's a very similar formula that you can use in M2:

=IF(ROWS($1:1)< MATCH(0.01,$C$2:$C$10)+1,"",INDEX(G:G,ROWS($1:1)-MATCH(0.01,$C$2:$C$10)+1))

but it looks at the first 10 cells of column C and moves column G down as appropriate.

Hope this helps.

Pete

Thankyou so much
I wonder if there could be a way to incorporate the shifting method into the binomial distribution function, so that it prints out starting at the row matching the first integer (non-zero value) of the y-axis: counts per bin.

Your file only had values in the cells for the binomial distributions - what formulae did you use to generate them?

Pete

I used BINOM.DIST(number_s,trials,probability_s,cumulative), where number_s=ColE=0,1,2,... ; trials=length of ColB from the first to last integer including the zeros in between but not at the ends, probability_s=0.5, cumulative=false.

You do not have to use the above equation, If you could printout anything (list of numbers, etc) that start printing out at the same row of the first non-zero value of ColB, I could use the idea to do the same with the binom.dist function.

Assume that you know the length of the y-axis: counts per bin. I use another column to find the cumulative counts per bin. From here, it is easy to count the length, because the min value is 0% and the max value is 100%.

You were looking for something to do this automatically over a number of different files, so I was interested in how you derived the two distributions. For the trials parameter, did you manually inspect the values in column B to find the first and last non-zero values to derive the range, or did you use a formula for this (it would need to be a formula if you want to automate the process).

Just to confirm things, then, you start off with data in columns A to C of your attached file, and you want to end up with what you show in columns K and L?

Pete

=IFERROR(MAX(M$2:M$64)*BINOM.DIST($W2,COUNTIF(M$2:M$64,"<"&MAX(M$2:M$64))-COUNTIF(M$2:M$64,"<"&1),0.5,FALSE),"")

where:
ColM is the cumulative sum of counts per bin
ColW is the counter: 0,1,2,3,...
I calculated the trials parameter by subtracting the counts less than the max of the ColM subtract the counts less than the value of 1

I used the above formula to calculate the binomial distribution

Hi Pete:
I have been researching how to solve the problem. I guess I am giving up on it but your first solution works very well already, so I do not need to pursue it any further. Thanks, I will be using your first solution. Have a good day!

Okay, then - glad you got what you wanted.

Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete