Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Anonymous User
  Tuesday, 20 September 2022
  2 Replies
  4.2K Visits
0
Votes
Undo
Afternoon:

Have an Excel Workbook specifically made (not a template)
have 12 Sheets - 1 for each month broken down into Monday - Friday weeks
Have a master sheet that totals numbers from each week and places them into a cell on the Master sheet (Think year to date)
This works fine

Trying to:
Take that number on the Master sheet that changes from week to week
Divide that number by the Week Number (38, 39,40, etc)
and place that average into a cell on the specific month for that corresponding week. (weekly average)

Tried almost everything, but all it displays is a Date
so using generic made up numbers I'll use the existing formula
I have an empty cell on each week of each worksheet that places the week number, for this exercise I'm using October 3-7 (Mon-Fri) Week 41, That "Week 41" cell's formula is: =WEEKNUM(B1,2) (Takes date from B1,Monday and determines the week number. placing it into A3 (can be any cell)
I submit weekly reports, and it's now wanted to have the "average"
I need to take the total from the master sheet ='2022 Master'!B17 (B17 is the cell where the number goes , it's a year to date running total,
divide that number by the week number from October's corresponding week and place the calculation into O19 beside the specific week.
All I'm getting is a date, no matter what I've tried

For those following along, the next week would be 42 so it should take the increased total from master sheet, divide by 42 and place
it in O44 and so on. the results cell can be anywhere on the page.

Any ideas on how to do this without reformatting a whole bunch of cells?,
How do you divide 2 cells that have formulas in them to determine a total in another cell...

Thanks in advance
1 year ago
·
#3081
0
Votes
Undo
Hi there,

Could you please attach a sample file? So that we can better understand your question :)

Amanda
1 year ago
·
#3082
0
Votes
Undo
Year to date (Master) Total.png Weekly File sheet.png Year to date (Master) Total.png Will try to explain better, have included 2 graphics so that the general gist can be obtained, hard to supply
a graphic that I'm allowed to...
Weekly Graphic:
Files are placed each day into the appropriate column (file count changes daily)
Files are totaled each day in the Blue line at bottom and placed into the green line
At end of week, all the weekly files are totalled (number under the green at bottom)(52)
That number (52) is transferred to and added to the year to date total on Year to date (Master) Sheet
(This is dynamic, and the master sheet total (B17) changes each day)

What I am looking for to do is to provide a weekly average for the specific week (38,39,40 etc)
and place it either beside the weekly total or monthly total (cell placement doesn't matter, can be any cell)
So the formula when trying to do this is:

(B17 is the cell number where the year to date number is on the Master sheet)
when I choose a blank cell on the weekly sheet and use the formula
='2022 Master'!B17/39 it should place the result of the number in the cell
from my understanding, that formula is supposed to take the large number on the Master sheet at B17 and divide it by the week number (39) to get an average for the week.

All it gives me is a date and not even todays date...

The weekly average now needs to be included with each week, so if there's a simpler way to do this, then I'm all ears
some formula that will take the master sheet year to date files number and divide by the corresponding weekly number (38, 39,40 etc)
if I have to place this in a cell for each week then fine, but thought there was a better way, because there's always a better way right?
Currently VB is not an option, so for now it's manual.... Weekly File sheet.png
  • Page :
  • 1
There are no replies made for this post yet.