Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Profil ag30rox
 | Femeie
25 ani
Arges
cauta Barbat
32 - 46 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
I have an Excel file containing 10 sheets, 1 sheet is to be a dashboard and the other 9 sheets contain data.
The data sheets:
- they all have the same format, meaning that they all contain a table of 4 columns, A to D
- each sheet contains multiple chapters / sections and each chapter contains one or more criteria
- chapter names are on merged row cells, meaning that chapter 1 is merged on the entire row on columns A to D
- each criteria is graded in column C
- each chapter has a "TOTAL" row which calculates an average of the grades of its criteria
- each chapter "TOTAL" row is merged on columns A and B, so the words "TOTAL" show in column A
- some sheets have 4-5 chapters meaning 4-5 totals rows, some sheets have more than 10 chapters and the same number of totals
- in some sheets criteria hasn't been graded and the TOTALs show a #DIV/0! error
- each sheet has a percentage calculated at the end of the sheet by summing all TOTALs and dividing the sum by the number of TOTALs
- the percentage is located at the end of column C that contains the TOTALs
- the TOTALs in each sheet doesn't count the TOTALs with the #DIV/0! errors
- I cannot add, remove or modify things from sheets, like add formulas, change formulas etc.
Why is it not OK to sum each sheet's percentage and divide the sum by the number of percentages?
- summing the percentages and then dividing the resulting total percentage by the count of percentages (in my case 9) would not give an accurate representation of the overall average percentage
- the reason is that the number of TOTALs from each sheet and their respective grades are not equal across all sheets (one sheet has 4 TOTALs and the other has 15 TOTALs)
- each sheet contributes differently to the overall average, and the division by the number of sheets alone does not account for these variations
- by summing the percentages for each sheet divided by the count of percentages, you would be effectively giving equal weight to each sheet, regardless of the number of TOTALs or their grades
- this can skew the overall average since some sheets with more TOTALs or higher grades would have a greater impact on the result
- to obtain an accurate overall average percentage, it is necessary to consider the total number of TOTALs and sum of all the corresponding grades in all the sheets
(~ courtesy to ChatGPT for a great explanation)
The dashboard sheet:
- I created 4 columns, A to D, in column A data is entered manually and in the rest there are formulas:
- column A is helper column and contains the name of each sheet on each row
- column B calculates the last row in each sheet
- column C calculates the sum of TOTALs from each sheet without summing the cells that show a #DIV/0! error (cause it would make the formula error)
- column D calculates the number of TOTALs from each sheet minus the number of TOTALs that show a #DIV/0! error
- Here are the formulas I used:
- cell A10: contains the first sheet name which is: One
---- sheet names added manually ---- if you can edit sheets, this could be done using a formula added in each sheet far away from the data, in cell X1 for example, that extracts sheet name: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)
- cell B10: =LOOKUP(2;1/(INDIRECT("'" & $A10 & "'!" & "A:A")="TOTAL");ROW(INDIRECT("'" & $A10 & "'!" & "A:A")))
- cell C10: =SUMIFS(INDIRECT("'" & $A10 & "'!" & "C1:C" & $B10); INDIRECT("'" & $A10 & "'!" & "C1:C" & $B10);"<>#DIV/0!"; INDIRECT("'" & $A10 & "'!" & "A1:A" & $B10);"TOTAL")
- cell D10: =COUNTIF(INDIRECT("'" & $A10 & "'!" & "A1:A" & $B10);"TOTAL") - COUNTIF(INDIRECT("'" & $A10 & "'!" & "C1:C" & $B10);"=#DIV/0!")
- Explanation:
- cell A10: no need
- cell B10: extracts the number of the row containing the last TOTAL in each sheet; instead of "A:A" you could also use eg "A1:A100"
- cell C10: let's assume that the first sheet is named One and is in A10, and the last row is 29 which is in B10: using SUMIF with INDIRECT to sum the value of each TOTAL in the sheet: =SUMIFS('One'!C1:C29; 'One'!C1:C29;"<>#DIV/0!"; 'One'!A1:A29;"TOTAL")
- cell D10: as above, A10 = One and B10 = 29, using COUNTIF with INDIRECT to count the number of TOTALs in each sheet, then subtract the TOTALs which have a #DIV/0! error: =
=COUNTIF('One'!A1:A29;"TOTAL") - COUNTIF('One'!C1:C29;"=#DIV/0!")
- I dragged all formulas (except for column A where the data is manually added) down to row 18
- The total percentage is the sum of the sum of TOTALs from column C divided by the sum of the count of TOTALs from column D
- cell C4: =SUM(C10:C18)/SUM(D10:D18)
_______________________________________
|
|
| pus acum 1 an |
|