Need help with excel function?

Question

Hi I need to determine the number (frequency) of employees who worked less than 20 hours, between 20 and 29 hours, between 30 and 40 hours and over 40 hours. Cell J28: J31 list the ranges. You need to translate this range into correct values for the Bin column in cells I28:I30 and then enter the frequency function in the cells K28:K31.

How do I write this formula? I have tried countless times, but I am not getting the correct number that the teacher posted.

Can anyone help me?
I know the formula is Frequency(data_array, bin_array)
Am I missing a step?
--thanks!

Answer 1

With the FREQUENCY command, you will need to do this:



Whatever your data array is (your list of hours for each employee) will be the first range in the formula.

Let's say that's A2:A20 for this example.



Now, with your bins array, and I'm assuming this will be the range I28:I30 (with values of 20, 30, 40.)



Now, select each cell in the J range.. K28:K31



Now type your formula: =FREQUENCY(A2:A20, J28:J30)



Now, press CTRL + SHIFT + ENTER.



You will now have totals for each set. And, of course, replace A2:A20 with the actual range where your hours are listed.

Answer 2

Recall that the "frequency" function is an array function, and that a range is selected first(e.g. K28:K31) then the function is entered into the active cell, and after the arguments are entered, Ctrl + Shift + Enter is used to enter the formula. Also, the bin values are upper limits.

Answer 3

Hi, I would suggest you to download a exced add in. Just open the excel, and click the main top icon. Then you click excel options, and then in the emerging screen, click adds in. There in the manage box, (at lower end), just select excel adds in from the drop down menu. a box opens, just select analysis tool pak and then what you need and then click ok. Allow excel to download this add on from your installation office directory. It is automatic. Do allow few minutes. Once you download, the data analyis tab will be found in the DATA main tab. This is first step.

Secondly, suppose you are having the data in one column. Select the data, go to this data analysis tool pak, and then click histogram. It is useful tool for frequency ok.

It will open one box, in the first box, it will show the range you selected of hours column. Just select the next column, the same range in the next column (this is called bin),but leave the last row as blank. for eg. if your selection is a1 to a9 then, b1 to b8.This will show in the histogram screen. Just click ok. and you get all the frequency correctly in another sheet in the same work book.

Remember to select as i said. you could also mark a chart if you want for this. Hope enjoy

Answer 4

Actually To Get the highest command On Excel Is now an Easy task, you get your own reliable and Dynamic dashboard the best amazing tutorial or you can Ask Get more exciting dashboard Join this group.https://www.facebook.com/groups/exceladvise/?fref=ts hope it will help you take care!





Check this exceladvise.com