on sheet 2 i have monthly tables of hours worked for 8 people. On the side there a table for monthly averages.
how do i autofill the averages of the monthly charts into my average charts. its not arrange in way that the relative cell reference would shift automatically into the correct cell/range.practice income sheet.xlsx
this should have it
Last edited by businpro; 11-09-2017 at 10:05 AM. Reason: wrong upload
Welcome to the forum!
You seem to have uploaded the wrong workbook - there is nothing on sheet 2.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
i had some progress. the problem is(i think) once you stick the column# for address
into indirect it doesn't stay relative.practice income sheet.xlsx
how do make the column relative so i can autofill a 2D table?
If I understand correctly the following formula will yield the average time:Formula:
Please Login or Register to view this content.
Select cell R3 and paste the formula into the formula bar,
Copy the formula over to Y3,
While the range R3:Y3 is still selected copy down to Y14,
*While the range R3:Y14 is still selected apply custom formatting [h]:mm to the range.
*This last step may not be needed.
Note: in cell Q9 change Jul to July.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks this worked. now i need to figure how.
i almost got it done with [=average(indirect(address(match......),match(.....),2,1):indirect(address(match(....),match(...),2,1)]
to get the column for addresses to stay relative, used match and clicked any cell in that column and matched in its column #.
for some reason its showing me N/A in random spots, and i dont understand the formula evaluation.
the asteriks in the sumproduct are not multiplying.
its taking the third array and selecting a part of it corresponding to vertical and horizontal ranges.
when i watched a sumproduct video, it just replaced an array formula summing the products of two corresponding arrays. e.g. sumproduct(A1:A5,B1:B5). Does sumproduct have one complex formula which you can methodically use separatedly or does it actually have more than one formula?
This article explains some of the applications of the SUMPRODUCT function. The section titled "Applying Multiple Criteria to SUMPRODUCT Function", which is about halfway down the page, is probably the best description of the way it is being used in your spreadsheet.
Let us know if you have any questions.
if i did not have the abbreviated months column in the source table, and i want to use the asterik as a wild-card for the average-table's months, the problem is that the cell value isnt "january.....", rather its 1/../.. formatted as text. how do i get the actual value to be "Wed January ...." and get the calendar autofill?
If I understand you want the formula to be written so that it accesses the dates directly from column E.
Try the following:Formula:Please Login or Register to view this content.
In this case you will need to change cell Q9 back to Jul
Let us know if you have any questions.
Yes but i would like to get rid of the F column. i would need to use the text formula for countif also but it didn't work.
Also, could you explain how i could use the following formulas: average(indirect(address(match....),match(...),2,1)):indirect(address(match(...),match(...),2,1)))
i tried it but it displayed #N/A on certain cells and i couldn't figure why.
The following formula doesn't need column F:Formula:Please Login or Register to view this content.
Set the format to either h:mm or [h]:mm
Let us know if you have any questions.
Edit: If you can show me the entire formula, fill in the ...'s, I will attempt to look at why it yields the #N/A errors later.
Last edited by JeteMc; 11-15-2017 at 01:30 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks