SharePoint List Formatting
Back to view formats
View formattingSharePoint Online

Numeric Average Comparison

Provides an example of comparing numeric values across time periods. In this example, we are using grades for students across 4 quarters, though the principle can be applied to any repetitive numeric values.

Get this format View on GitHub

Shared by David Warner II
Updated Nov 7, 2018

Numeric Average Comparison formatting preview
Sample preview · View original image

Copy. Apply. Make it yours.

Get this format

1 definition

Review the requirements below, then paste this JSON into the matching formatting panel. Show me where.

Use your list's internal column names and replace any tenant-specific URLs or flow IDs. Formatting is presentation, not a way to secure data.

View formatting
Download JSON
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "hideSelection": true,
  "hideColumnHeader": true,
  "rowFormatter": {
    "elmType": "div",
    "attributes": {
      "class": "sp-row-card"
    },
    "children": [
      {
        "elmType": "div",
        "style": {
          "text-align": "left",
          "color": "#f99500",
          "width": "100%"
        },
        "children": [
          {
            "elmType": "div",
            "attributes": {
              "class": "vf-StudentGrades-StudentName"
            },
            "style": {
              "color": "#772d96",
              "font-size": "20px",
              "margin-bottom": "10px"
            },
            "children": [
              {
                "elmType": "span",
                "attributes": {
                  "class": "vf-StudentGrades-NumericGradeLabel"
                },
                "txtContent": "Student Name: "
              },
              {
                "elmType": "span",
                "attributes": {
                  "class": "vf-StudentGrades-NumericGradeValue"
                },
                "style": {
                  "font-weight": "bold"
                },
                "txtContent": "=toString([$Title])"
              }
            ]
          },
          {
            "elmType": "div",
            "attributes": {
              "class": "vf-StudentGrades-MasterWrapper"
            },
            "children": [
              {
                "elmType": "div",
                "attributes": {
                  "class": "vf-StudentGrades-Q1Grade-Wrapper vf-StudentGrades-Common-Wrapper"
                },
                "style": {
                  "float": "left",
                  "width": "22%",
                  "height": "155px",
                  "border": "1px solid black",
                  "margin-right": "1%",
                  "padding-left": "1%",
                  "background-color": "#772d96"
                },
                "children": [
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-QuarterLabel"
                    },
                    "style": {
                      "text-align": "center",
                      "font-weight": "bold",
                      "font-variant": "petite-caps"
                    },
                    "txtContent": "1st Quarter"
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-LetterGrade-Wrapper"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGradeLabel"
                        },
                        "style": {
                          "text-align": "center",
                          "font-weight": "bold",
                          "display": "none"
                        },
                        "txtContent": "Q1 Letter Grade"
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGrade"
                        },
                        "style": {
                          "font-weight": "bold",
                          "font-size": "55px",
                          "text-align": "center"
                        },
                        "txtContent": "=if([$Q1NumericGrade] >= 99, 'A+', if([$Q1NumericGrade] >= 90, 'A', if([$Q1NumericGrade] >= 88, 'B+', if([$Q1NumericGrade] >= 80, 'B', if([$Q1NumericGrade] >= 78, 'C+', if([$Q1NumericGrade] >= 70, 'C', if([$Q1NumericGrade] >= 69, 'D+', if([$Q1NumericGrade] >= 60, 'D', if([$Q1NumericGrade] >= 0, 'F', 'Next')))))))))"
                      }
                    ]
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-NumericGrade"
                    },
                    "style": {
                      "font-size": "15px"
                    },
                    "children": [
                      {
                        "elmType": "span",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeLabel"
                        },
                        "txtContent": "Q1 Numeric Grade: "
                      },
                      {
                        "elmType": "span",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeValue"
                        },
                        "style": {
                          "font-weight": "bold"
                        },
                        "txtContent": "=toString([$Q1NumericGrade]) + '%'"
                      }
                    ]
                  }
                ]
              },
              {
                "elmType": "div",
                "attributes": {
                  "class": "vf-StudentGrades-Q2Grade-Wrapper vf-StudentGrades-Common-Wrapper"
                },
                "style": {
                  "float": "left",
                  "width": "22%",
                  "height": "155px",
                  "border": "1px solid black",
                  "margin-right": "1%",
                  "padding-left": "1%",
                  "background-color": "#772d96"
                },
                "children": [
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-QuarterLabel"
                    },
                    "style": {
                      "text-align": "center",
                      "font-weight": "bold",
                      "font-variant": "petite-caps"
                    },
                    "txtContent": "2nd Quarter"
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-LetterGrade-Wrapper"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGradeLabel"
                        },
                        "style": {
                          "text-align": "center",
                          "font-weight": "bold",
                          "display": "none"
                        },
                        "txtContent": "Q2 Letter Grade"
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGrade"
                        },
                        "style": {
                          "font-weight": "bold",
                          "font-size": "55px",
                          "text-align": "center"
                        },
                        "txtContent": "=if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 99, 'A+', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 90, 'A', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 88, 'B+', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 80, 'B', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 78, 'C+', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 70, 'C', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 69, 'D+', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 60, 'D', if(([$Q1NumericGrade] + [$Q2NumericGrade])/2 >= 0, 'F', 'Next')))))))))"
                      }
                    ]
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-NumericGrade"
                    },
                    "style": {
                      "font-size": "15px"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeAvg"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q1-Q2 Average Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString(([$Q1NumericGrade] + [$Q2NumericGrade])/2) + '%'"
                          }
                        ]
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeQuarter"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q2 Numeric Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString([$Q2NumericGrade]) + '%'"
                          }
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "elmType": "div",
                "attributes": {
                  "class": "vf-StudentGrades-Q3Grade-Wrapper vf-StudentGrades-Common-Wrapper"
                },
                "style": {
                  "float": "left",
                  "width": "22%",
                  "height": "155px",
                  "border": "1px solid black",
                  "margin-right": "1%",
                  "padding-left": "1%",
                  "background-color": "#772d96"
                },
                "children": [
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-QuarterLabel"
                    },
                    "style": {
                      "text-align": "center",
                      "font-weight": "bold",
                      "font-variant": "petite-caps"
                    },
                    "txtContent": "3rd Quarter"
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-LetterGrade-Wrapper"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGradeLabel"
                        },
                        "style": {
                          "text-align": "center",
                          "font-weight": "bold",
                          "display": "none"
                        },
                        "txtContent": "Q3 Letter Grade"
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGrade"
                        },
                        "style": {
                          "font-weight": "bold",
                          "font-size": "55px",
                          "text-align": "center"
                        },
                        "txtContent": "=if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 99, 'A+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 90, 'A', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 88, 'B+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 80, 'B', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 78, 'C+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 70, 'C', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 69, 'D+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 60, 'D', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 >= 0, 'F', 'Next')))))))))"
                      }
                    ]
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-NumericGrade"
                    },
                    "style": {
                      "font-size": "15px"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeAvg"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q1-Q3 Average Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade])/3 ) + '%'"
                          }
                        ]
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeQuarter"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q3 Numeric Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString([$Q3NumericGrade]) + '%'"
                          }
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "elmType": "div",
                "attributes": {
                  "class": "vf-StudentGrades-Q4Grade-Wrapper vf-StudentGrades-Common-Wrapper"
                },
                "style": {
                  "float": "left",
                  "width": "22%",
                  "height": "155px",
                  "border": "1px solid black",
                  "margin-right": "1%",
                  "padding-left": "1%",
                  "background-color": "#772d96"
                },
                "children": [
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-QuarterLabel"
                    },
                    "style": {
                      "text-align": "center",
                      "font-weight": "bold",
                      "font-variant": "petite-caps"
                    },
                    "txtContent": "4th Quarter"
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-LetterGrade-Wrapper"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGradeLabel"
                        },
                        "style": {
                          "text-align": "center",
                          "font-weight": "bold",
                          "display": "none"
                        },
                        "txtContent": "Q4 Letter Grade"
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-LetterGrade"
                        },
                        "style": {
                          "font-weight": "bold",
                          "font-size": "55px",
                          "text-align": "center"
                        },
                        "txtContent": "=if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 99, 'A+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 90, 'A', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 88, 'B+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 80, 'B', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 78, 'C+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 70, 'C', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 69, 'D+', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 60, 'D', if(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4 >= 0, 'F', 'Next')))))))))"
                      }
                    ]
                  },
                  {
                    "elmType": "div",
                    "attributes": {
                      "class": "vf-StudentGrades-NumericGrade"
                    },
                    "style": {
                      "font-size": "15px"
                    },
                    "children": [
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeAvg"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q1-Q4 Average Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString(([$Q1NumericGrade] + [$Q2NumericGrade] + [$Q3NumericGrade] + [$Q4NumericGrade])/4) + '%'"
                          }
                        ]
                      },
                      {
                        "elmType": "div",
                        "attributes": {
                          "class": "vf-StudentGrades-NumericGradeQuarter"
                        },
                        "children": [
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeLabel"
                            },
                            "txtContent": "Q4 Numeric Grade: "
                          },
                          {
                            "elmType": "span",
                            "attributes": {
                              "class": "vf-StudentGrades-NumericGradeValue"
                            },
                            "style": {
                              "font-weight": "bold"
                            },
                            "txtContent": "=toString([$Q4NumericGrade]) + '%'"
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
}
View sourceLink to this definitiongeneric-numeric-average-comparison.json

From the contributor

How to use this sample

Summary

Provides an example of comparing numeric values across time periods. In this example, we are using grades for students across 4 quarters, though the principle can be applied to any repetitive numeric values.

The View Formatting definition calculates the average of the current and prior quarters numeric grades. The current quarter numeric grade and the average of the previous quarters is displayed. (Example: Quarter 3 calculates the average of quarter 1, 2 & 3)

In addition to the numeric average calculation displayed within each quarter, this example also illustrates how the average value in each quarter can be used to assign additional labels, such as the Student Grade. (Example: A,B,C,D,F)

screenshot of the sample

View requirements

Type Internal Name Required
Single line of text Title Yes
Single line of text StudentName No
Number Q1NumericGrade Yes
Number Q2NumericGrade Yes
Number Q3NumericGrade Yes
Number Q4NumericGrade Yes

Unformatted View

Sample

Solution Author(s)
generic-numeric-average-comparison.json David Warner II

Version history

Version Date Comments
1.0 November 7, 2018 Initial release

Disclaimer

THIS CODE IS PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.

Additional notes

Using the customRowAction with an action of defaultClick creates a great way to make your list into a master view with details easily accessible. This sample wraps the entire row in a button so that you can click anywhere in the row to open the information panel for the item: