Calculation graph is a sign counting table to present the frequency at which something happened. Microsoft Excel has a large number of default chart types available, but does not have graph calculation options. Luckily, this can be made using Excel formulas.
For this example, we want to make a calculation graph to visualize the votes received by each person on the list.
Create a Tally system
Calculation charts are usually presented as four lines followed by a diagonal line through the lines for the fifth calculation. This provides a good visual grouping.
It is difficult to replicate this in Excel, so instead, we will group the values by using four pipe symbols and then hyphens. The pipe symbol is a vertical line above the backslash character on a US or UK keyboard.
So, each group of five will be displayed as:
And then a single pipe symbol for a single occurrence (1) will appear as:
Type these symbols into cells D1 and E1 in a spreadsheet.
We will graph the calculations using the formulation and reference of these two cells to display the correct calculation marks.
Add up Group Five
For a total of five groups, we will round up the votes to the nearest multiple of five and then divide the results by five. We can use a function called FLOOR.MATH to round up the value.
In cell D3, enter the following formula:
This rounds the value in C3 (23) to the nearest multiple of 5 (20) and then divides the result by 5, giving an answer of 4.
Total Remaining Singles
We now need to calculate what is left after group five. For this, we can use the MOD function. This function returns the remainder after two numbers have been divided.
In cell E3, enter the following formula:
Create Tally Graph with Formula
We now know the number of groups of five and also the number of singles to display in the calculation graph. We just need to combine them into one line of counting marks.
To do this, we will use the REPT function to repeat the appearance of each character, how many times it takes, and combine them.
In cell F3, enter the following formula:
The REPT function repeats text several times. We use a function to repeat the character count the number of times determined by a group and a single formula. We also use ampersands (&) to combine them together.
Hide the Helper Column
To complete the calculation graph, we will hide the helper columns D and E.
Select columns D and E, right-click, then select “Hide.”
Our complete calculation graph provides a good visual presentation of each vote received by each person.