I'm creating a worksheet for a service desk which stats are taken once a month and then extrapolated for the rest of the month. I've created a calendar that will count the number of days that the service desk is open for the different hours of the week, see worksheet file. I'm having a problem with cell references in the formulas. I'm actually using two sheets one with the service desk statistics and the second sheet that counts the different hours and days of the month to find the total service hours for anyone month but for my example file is based on one worksheet though I'm still having the same problem. The formula I've tried using is =B2/72*'Stats Days'!J41. I've also tried putting all numbers in its own cell as well and still have the same results. I believe the number are rounded up to the next whole number but I don't see why the totals are so different. How can have the formula round the numbers to the next whole number. The cells are formatted to Number with no decimal numbers. Thanks for the assistance. Daivd
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Formula for cell references are not correct.
- Thread starter davidb73
- Start date
Hi:
I am afraid I fully understood what you to want to achieve here the formula, =B2/72*'Stats Days'!J41 is not making much sense to me as you do note have any value in cell J41 and B2 in your sheet is a text value, you cannot do mathematical calculation on a text value. To round the results to next whole number you can use any of the these formulas ROUND, ROUNDUP or ROUNDDOWN as per your need.
Thanks
I am afraid I fully understood what you to want to achieve here the formula, =B2/72*'Stats Days'!J41 is not making much sense to me as you do note have any value in cell J41 and B2 in your sheet is a text value, you cannot do mathematical calculation on a text value. To round the results to next whole number you can use any of the these formulas ROUND, ROUNDUP or ROUNDDOWN as per your need.
Thanks
NARAYANK991
Excel Ninja
Hi David ,
I am in the same boat as Nebu !
I am able to understand the following :
1. The dates in column B represent the 31 days of January 2015. The cells have been formatted to show the day of the week.
2. Cells C36 , D36 and E36 show the number of Sundays ( 4) , number of days that are Mondays , Tuesdays , Wednesdays or Thursdays ( 17 ) and the number of days that are Fridays or Saturdays ( 10 ).
3. Since the number of hours per day varies according to which day of the week it is , multiplying the numbers in (2) above gives the results in cells G36 , H36 and I 36 as :
G36 = 4 * 6 = 24
H36 = 17 * 12 = 204
I36 = 10 * 9 = 90
Adding all of these gives 318.
What is the data entry value of 843 in cell H3 ?
What is the calculated figure in I3 , which is obtained as follows :
=H3 * 318 / 72
318 is the number of service hours in the month of January ; 72 is the number of service hours in a week.
Dividing 318 by 72 will therefore give the number of weeks in the month of January.
What is obtained by multiplying 843 by this ?
If we go about it the way you have done , what do we get by dividing 843 by 72 ? 843 has been entered in a column labelled Monthly Tally ; dividing a monthly figure by the number of service hours per week , will give some strange measure of tally per service hour per week ; how can we now multiply this by service hours per month ?
Can you explain what exactly you want to do ?
Narayan
I am in the same boat as Nebu !
I am able to understand the following :
1. The dates in column B represent the 31 days of January 2015. The cells have been formatted to show the day of the week.
2. Cells C36 , D36 and E36 show the number of Sundays ( 4) , number of days that are Mondays , Tuesdays , Wednesdays or Thursdays ( 17 ) and the number of days that are Fridays or Saturdays ( 10 ).
3. Since the number of hours per day varies according to which day of the week it is , multiplying the numbers in (2) above gives the results in cells G36 , H36 and I 36 as :
G36 = 4 * 6 = 24
H36 = 17 * 12 = 204
I36 = 10 * 9 = 90
Adding all of these gives 318.
What is the data entry value of 843 in cell H3 ?
What is the calculated figure in I3 , which is obtained as follows :
=H3 * 318 / 72
318 is the number of service hours in the month of January ; 72 is the number of service hours in a week.
Dividing 318 by 72 will therefore give the number of weeks in the month of January.
What is obtained by multiplying 843 by this ?
If we go about it the way you have done , what do we get by dividing 843 by 72 ? 843 has been entered in a column labelled Monthly Tally ; dividing a monthly figure by the number of service hours per week , will give some strange measure of tally per service hour per week ; how can we now multiply this by service hours per month ?
Can you explain what exactly you want to do ?
Narayan
Thank you for the messages. My apologies. Narayan as you point out is what I'm trying to do. In my workbook the sheet that handles the calendar and the total number of service hours per month is a second sheet titled Stats Days that will work out to give you the number 318 for January and 288 for February. I copied the portion of the service entry data on the top right since this is the section I'm having trouble with on worksheet 1 which has more entries the service desk enters. So, The formula I showed =B2/72*'Stats Days'!J41 is off the original sheet. B2 is the service desk's entries on sheet one. My apologies, in the example that will work out as the entry for H3 instead of B2 and is used for the formula in I3 to be =SUM(H3/72*J36). The number in J36 would actually come off the second sheet which is actually J41. I wanted to show the actual formula I was actually trying to use. I guess I should have uploaded the whole workbook but thought shortening it to an example sheet would be easier, sorry for confusion. With that, the number 843 in H3, 655 in H4 and 270 in H3 are the numbers reported by the service desk they handled in each category. I'm suppose the take the number tallied 843 and dividing it by 72 the actual weekly service hours to get what is considered the average number of inquiries per hour and then using the total monthly service hours 318 will give what should be an accurate average across the month for each week. As vletm points out I need to round up the number while using the same formula and that is where I'm having trouble with. I hope this helps. Thanks, David
I cant figure out why I'm getting two different number when I use the formula =SUM(H3/72*J36) when there is a cell reference to J36, I getting 3723. If I put the a straight forward formula in as =SUM(12*318) the result is 3816 which is correct. I hope this helps to make it easier to know what I'm suppose to do. Thanks.