Hello,
I trying to make a PivotTable and Chart for my grades achieved at school. I have made a concept in Excel (see attached). The grade list is provided with both number (from 1 to 10) and letters (F= Fail, P= Pass, G= Good). How can this been displayed at my PivotTable? At the moment I got the #DIV/0! error message because Excel want to see a number format (I guess...).
For a better understanding:
"Math 11" = Exam number 1 at Year 1.
"Math 21" = Exam number 1 at Year 2.
etc.
Like to hear!
Last edited by luukos97; 01-22-2021 at 11:58 AM.
Hi there,
You are correct. A pivot table can't 'avarage' a non-numeric value like F, P, G. This give you the error.
You also can't summarise non-numeric values.
Work around:
Split the Grades and Marks in separate columns.
Give the Marks a specific numeric value. For example F = 5, P = 10, G = 15 (you could use 1,2,3,... make it representative in relations to the other marks, maybe Fail 3, Pass 6, Good 9...so they align when greating the pivot table)
Use the column with the 'numerised' grade in your pivot table, summerise as MAX (you could use MIN, SUM or AVERAGE as long as the entries are unique and won't be adding multipe Marks into the one cell.
Now the tricky bit; use conditional formatting to convert the numbers to Fail, Pass, Good.
With your cursorin the pivot table in cell C4, go to the Home tab and click Conditional Formatting, then New Rule.
At the top of the dialog box, select; All cells showing "Max of Mark" values.
In the middle section, select: Use a formula to determine....
In the bottom box enter =D4=15 (sample for 'Good')
Then click 'Format'
In the format dialog box select the 'Number' tab, then 'Custom' at the bottom of the list on the left.
Under Type, repalce 'General' with: [=15]"Good";;
Click Ok, then Ok again.
Repeat above steps for Pass and Fail.
Finally, add another simple conditional formatting. if 0 then Font = white (invisible).
See attached file for result.
Last edited by ORoos; 01-23-2021 at 12:58 AM.
If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.
Hi ORoos,
Thank you for your explanation.
I have processed the comments in the sheet but I run into some issue's, see attached the edited .xlsx file.
In the PivotChart, the Max of Mark is shown, I prefer the chart without this value. But when I try to change it at the "Select data source" menu, I cant delete the entry....?
As well I tried to add a function in column D of the "Gradelist" worksheet. How can I add to formula into the whole column? When I do this, the numbers can't be filled as value. I added arrows to show where I inserted this formula. Besides, I think column "F" can be deleted because I prefer the value's in column "D".
Hi luukos97,
As the Pivot Chart is linked to the Pivot Table, remove the 'Max of Mark' from the Pivot table and it will also be removedfrom the Pivot Chart.
Column D currently shows your grades. They are manually entered. You can not have formulas and manual entered values in the same cell simultaniously.
If you want to enter numeric Grades and non-numeric Marks (F, P, G) into cell D, that is fine.
We use then column E to convert non-numeric entries into numeric values or return the original numeric value. You then use this column E in your Pivot table.
I have added a second sheet with a re-arranged pivot table (same data), which allows you to compare grades year by year...
Let me know if that is getting closer to what you need.
Definitely, this looks close to my demanding. I think some finetuning in the layout(formatting) and this can be displayed very nice on a dashboardLet me know if that is getting closer to what you need.
Thanks for the help, maybe some issue's will occur when I am going to format this, I will let you know if you don't mind
PS. I will come to the other topic as well. Hopefully tonight if I am not getting sleepy.
Hello ORoos,
I have processed most of my grades (from 2 studies) now but I can't figure out a how to deal with the F/P/G grades. Because at my bachelor study there were a lot of exam which where graded with F/P/G. These grades has more the meaning of conditionally (or provisory, did't know the english word). Now I have graded them with 3, 6 and 9 as you know but I doesn't feel comfortoble with it. Is there any option in Excel making these grades visuaal as provisory or something like that?
Like to hear
Hi Again,
Maybe have a look back at my initial replay in post #2.
Here I have used conditional formatting to show the values as FAIL, PASS, GOOD in the pivot table.
The logic was to translate the letters to numbers; F = 5, P = 10, G = 15 (you can use 3, 6, 9 if you prefer).
Important is that the other values are not amounting to the same as they are otherwise also translated to letters.
Instead of translating the numbers to letters, or in addition to it, we could highlight them in a different colour.
I am sorry but I do not understand it. Probably because I don't know exactly how I want to visualize the chart. I have added a chart in the PivotTable Worksheet where the orange line is the line for Fail/Pass/Good. The blue lines has open spaces because here the grade has a name (i.e. Practice, Year 1). Most complicated here is the Grade for Practice in Year 2, is has a numeric value (7.44) and a "Pass" value...
I just can't find a way to get a clear overview of this gradelist
Hi again,
A bit difficult to find a solution if you don't know how the outcome should be, what exactly you want to analyse or show.
We did use a translaton of F = 5, P = 10, G = 15, but that is not relative to the 1-10 scale of your other results. So I have changed this to F = 3, P = 6, G = 8. You can adjust this furhter if you think this is not reflecting the correct relevant to the other results. The aim here is to have these marks showing in line with the others. As it is from a different column, they still show in a different color/label so you know they are not 'normal' grades. I have also updated the heading in the pivot table to reflect this.
I have changed the chart a bit to show the results by year and added a second sheet with a chart by subject.
On the first chart 'Pivot by Year', I also added a Slicer. Here you can select one or more (hold the Ctrl key to add additional selections) for a less cluttered detail view by discipline. Same could be done for year etc....
See how you got with these pivot charts...
Thanks,
This is a good 'push' into the right direction. Preferred now is to create a chart which is by default showing my average grades OVERAL. I have added a worksheet into the Excel file.
The next step is hiding the empty fields in the chart. For example when Year 1 is selected, the fields "Practice" hasn't any value so it should be hided. As well I see when Year 3 is selected the outcome in the chart is quit strange because is has only two disciplines, any recommendations on the lay-out of the chart is welcome!
Last, I want to erase the grades which has F/P/G, I just want them in the Gradelist (table) but as I know they are only provisory, they shouldn't play any role in the chart in the end.
And ORoos, do you know if I can add a filter which can display the highest grade (to visualize my stengths) or a filter which display the highest AVERAGE grade?
Like to hear
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks