Excel Formula Help Need -- Lines per Hour

I looked around the internet to solve my formula problem but I get results on the basic formula that does not work for me. I am using Excel 2007.

I have a column that holds # of minutes (B2) and another column that holds # of lines (C2). I want to know how many lines per minutes.

The simple formula of =(C2*60)/B2 does not work because of the way I gather my data in column B and C.

Column B has this formula =SUMIF(Hours!A:A,$A2,Hours!D)
Column C has this formula =SUMIF(Data!C:C,$A2,Data!D)

So, I am unfortunately not getting my data directly from one cell such as the basic formula requires. I am computing it to only sum if the date is equal to the one in column A. Because my data involves minutes/formatting the result of 77 lines in 180 minutes is 36960.00 instead of 25.67.

I tried various formatting and can never get it to show 25.67. What am I missing?

 

Hi LilouNeedsHelp.

Have you solved this yet.?

Nothing wrong with anything here except your formula and your expected answer.

A long explanation follows - you may just prefer to skip that and go to "SO:" near the end.


If you are really calculating lines per minute, and you have 77 lines in 180 minutes, that equals 77/180 or 0.43 lines per minute, not 25.67 - that figure is lines per HOUR, from: (77/180x60), which is the same as (77*60/180), which is the same as (C2*60/B2). Your answer is a factor of 60 more than it should be.

I notice that the source of your 'minutes' data is a sheet called 'Hours'. It looks like that sheet does have hours on it and you are trying to convert the hours to minutes in your formula. This would be OK, but you are doing it the wrong way (if that is what you are doing).

If B2 is in hours, your formula for lines per minute should be (C2/B2/60), or (C2/(B2*60)), because if B2 is in hours, you need to multiply that by 60 to make it minutes. You will see that you would just have the 60 in the wrong place in your formula.

But there is still something wrong... where did your answer of 36960 come from?
Your formula C2*60/B2 = 77*60/180 = 25.67
If your B2 is actually HOURS, Excel will be calculating 77*60/3 = 1540
Curiously, this is exactly 36960/24, and of course 24 just happens to be the number of hours in a day!!
Hence, 36960 is 60 times the number of lines per DAY, in your example. You would get this answer if you had hours in your Hours sheet, and the hours were being divided by 24 before being used in your formula.

SO:

it looks like Excel is calculating 77*60*24/3 or 77*60*(3/24) or 77*60*(180/60/24) = 36960

instead of 77/(60*3) or 77/180 = 0.43

Conclusion:

• change your formula to C2/B2/60 or C2/(B2*60), if B2 is in hours, or C2/B2 if B2 is in minutes;
• check your data - make sure your data is being read in hours or minutes, and that there is not a factor of 24 creeping in there somewhere.