benmoore89
New Member
- Joined
- Oct 27, 2015
- Messages
- 11
Hi everyone i hope someone can help me,
Firstly let me say that im fairly new to excel and have learnt some of the basics myself already but im looking for someone to help me create a formula which i have no cule.
To give you a bit of background info, i have a spreadsheet which contains data submitted from over 200 users in 50 departments who submit over 3000 reports in 7 different categories per month. to work out their average scores for the users and departments takes me nearly 2 days hence why im looking for some help.
What i want to do is work out what the users average score is out of all the submissions.
Currently i have come up with a formula to work it out manually once i use the data filter function. but this means i have to click 250 times on each of the 7 categories then enter the score manually.
hears a snapshot of one of the categories i'm referring to
<colgroup><col><col><col><col><col><col><col span="7"><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
for the columns which are supposed to equal 10 or 30 underneath the above i have to enter the following formula as the above one counts all '0'
=SUMPRODUCT(SUBTOTAL(3,OFFSET(N2:N270,ROW(N2:N270)-MIN(ROW(N2:N270)),,1)),ISNUMBER(SEARCH("10",N2:N270))+0)
i can then take the two away from eachother
then on the last row i have simply =sum(n_*5 10 30 0r 15) this allows me to add all up to work out what the total should be out of. then i can work out the average %
So instead of having to use data filter and the formula above, at the bottom of the table i have the 'User' name and this is where i want my formula to automatically work out what the average score is for all of the submissions by 'abbi' and then for the rest of the users.
you can help me with the one formula i can then adapt it to work out for the other users and then for the destinations
If you need me to explain this better then by all means let me know
THANKS IN ADVANCE :D
Firstly let me say that im fairly new to excel and have learnt some of the basics myself already but im looking for someone to help me create a formula which i have no cule.
To give you a bit of background info, i have a spreadsheet which contains data submitted from over 200 users in 50 departments who submit over 3000 reports in 7 different categories per month. to work out their average scores for the users and departments takes me nearly 2 days hence why im looking for some help.
What i want to do is work out what the users average score is out of all the submissions.
Currently i have come up with a formula to work it out manually once i use the data filter function. but this means i have to click 250 times on each of the 7 categories then enter the score manually.
hears a snapshot of one of the categories i'm referring to
| DatePosted | User | DTDestination | SKDestination | BSDestination | RepName | CI1 | CI2 | CI3 | CI4 | CI5 | CI6 | CI7 | CBCI1 | CBCI2 | CBCSQ3 | CBComplaint4 | CBSales5 | % |
| 9/20/2015 3:44:43 PM | crossleyabbi | na | ItalyNAP | na | Elaine | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | NA | 100% |
| 9/24/2015 6:11:52 PM | crossleyabbi | na | ItalyNAP | na | James | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | NA | 100% |
| 9/24/2015 6:12:33 PM | crossleyabbi | na | ItalyNAP | na | Racheal | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | NA | 100% |
| 9/24/2015 6:13:02 PM | crossleyabbi | na | ItalyNAP | na | Joanne | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | NA | 100% |
| 09/02/2015 13:36 | ahronstefanos | TenerifeTFS | na | na | Wolfgang | 5 | 5 | 5 | NA | 5 | 5 | 0 | 5 | 5 | 10 | 30 | 15 | 95% |
| 09/02/2015 13:39 | ahronstefanos | TenerifeTFS | na | na | Sandro | 5 | 5 | 5 | 5 | 5 | 5 | 0 | 5 | 5 | 10 | NA | 15 | 93% |
| 9/19/2015 8:32:36 PM | ahronstefanos | TenerifeTFS | na | na | Cedric | 5 | 5 | 0 | 5 | 0 | 5 | 0 | 5 | 5 | 0 | 0 | 0 | 30% |
| 9/19/2015 8:35:21 PM | ahronstefanos | TenerifeTFS | na | na | cedric | 5 | 5 | 0 | 5 | 0 | 5 | 5 | 5 | 0 | 0 | 0 | 0 | 30% |
| 9/20/2015 5:39:26 PM | ahronstefanos | TenerifeTFS | na | na | Ursa | 5 | 5 | 5 | 5 | 5 | 5 | NA | 5 | 5 | 10 | 30 | 0 | 84% |
| 9/30/2015 1:52:06 PM | bonita | na | na | TurkeyMarmaris | Karl | 5 | 5 | 5 | 5 | 0 | 0 | 5 | 5 | 5 | 10 | 30 | 15 | 90% |
| 9/30/2015 1:53:54 PM | bonita | na | na | TurkeyMarmaris | Michelle | 5 | 5 | 0 | 5 | 5 | 0 | 5 | 5 | 0 | 10 | 30 | 15 | 85% |
| 9/30/2015 1:55:24 PM | bonita | na | na | TurkeyMarmaris | Rosemary | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 0 | 10 | 30 | 15 | 95% |
| 9/30/2015 1:56:14 PM | bonita | na | na | TurkeyMarmaris | Ryan | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | 15 | 100% |
| 9/30/2015 1:58:14 PM | bonita | na | na | TurkeyMarmaris | Sophia | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | 15 | 100% |
| 9/30/2015 1:02:08 PM | zante1979 | na | na | Bodrum&IzmirBJV | Christopher | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 30 | NA | 100% |
<colgroup><col><col><col><col><col><col><col span="7"><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
for the columns which are supposed to equal 10 or 30 underneath the above i have to enter the following formula as the above one counts all '0'
=SUMPRODUCT(SUBTOTAL(3,OFFSET(N2:N270,ROW(N2:N270)-MIN(ROW(N2:N270)),,1)),ISNUMBER(SEARCH("10",N2:N270))+0)
i can then take the two away from eachother
then on the last row i have simply =sum(n_*5 10 30 0r 15) this allows me to add all up to work out what the total should be out of. then i can work out the average %
So instead of having to use data filter and the formula above, at the bottom of the table i have the 'User' name and this is where i want my formula to automatically work out what the average score is for all of the submissions by 'abbi' and then for the rest of the users.
you can help me with the one formula i can then adapt it to work out for the other users and then for the destinations
If you need me to explain this better then by all means let me know
THANKS IN ADVANCE :D
