How to

How to Make a Calculation Chart in Microsoft Excel

How to Make a Calculation Chart in Microsoft Excel

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.

1

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.

2

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:

=FLOOR.MATH(C3,5)/5

3

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:

=MOD(C3,5)

4

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:

=REPT($D$1,D3)&REPT($E$1,E3)

5

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.”

6

Our complete calculation graph provides a good visual presentation of each vote received by each person.

7

Related posts

How to Join Zoom Meeting Anonymously

Howto

How to Get on the NBA Fan Screen and Become a Virtual Fan using the Microsoft Team

Howto

How to Watch TV With Friends Using Hulu Watch Party

Howto

How to Use Chat Editor in Google Docs

Howto

How To Fix Green Screen Problem When Watching Videos

Howto

How to Remove Bloatware from a Chinese Cellphone without Root

Howto

How to View and Delete Zoom Recording

Howto

How to Use Amazon Music on Twitch Live Streams

Howto

How to update your BIOS

Howto