How to make a Bar Chart with this table or make a table for the Bar Chart without manually counting the Data

Nellycorn

New Member
Joined
Mar 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello,
Does anyone know how you can make a bar chart with this table?
1647622288474.png

Or how I can make a table from the data above ⬆️ that shows how many completed data, has more than the value with 5% tolerance, less than than the value with 5% tolerance or exact number of value, without manually counting it.
I want the bar chart to show how many completed data, has more than the value with 5% tolerance, less than than the value with 5% tolerance or exact number of value. e.g. the bar chart to look like this ⬇️ but without having to manually count the completed data like I did for this table to make this bar chart
1647621851528.png

I've made several attempts but the bar charts don't look like what I want it to be like the example bar chart above ⬆️
I would also appreciate it if you can let me know how I can do the same with a pie chart with the same data as well, e.g ⬇️
1647622556062.png
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Nellycorn

New Member
Joined
Mar 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello,
Does anyone know how you can make a bar chart with this table?
View attachment 60401
Or how I can make a table from the data above ⬆️ that shows how many completed data, has more than the value with 5% tolerance, less than than the value with 5% tolerance or exact number of value, without manually counting it.
I want the bar chart to show how many completed data, has more than the value with 5% tolerance, less than than the value with 5% tolerance or exact number of value. e.g. the bar chart to look like this ⬇️ but without having to manually count the completed data like I did for this table to make this bar chart
View attachment 60400
I've made several attempts but the bar charts don't look like what I want it to be like the example bar chart above ⬆️
I would also appreciate it if you can let me know how I can do the same with a pie chart with the same data as well, e.g ⬇️
View attachment 60402
Or if anyone knows a formula that count the number of <5% tolerance, >5% tolerance and = in each cell so it looks like the table I made to make the pie chart and bar chart above. I would appreciate that as well if my question can not be possible?
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
tolerance
Add a space in front of your 3 categories !!! (otherwise excel 'll not consider them as text
Cell Formulas
RangeFormula
E3:E5E3=COUNTIF($B$2:$B$39,D3)
E6E6=COUNTA($B$2:$B$39)
B2:B39B2=INDEX($D$3:$D$5,RANDBETWEEN(1,3))
 
Solution

Nellycorn

New Member
Joined
Mar 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
tolerance
Add a space in front of your 3 categories !!! (otherwise excel 'll not consider them as text
Cell Formulas
RangeFormula
E3:E5E3=COUNTIF($B$2:$B$39,D3)
E6E6=COUNTA($B$2:$B$39)
B2:B39B2=INDEX($D$3:$D$5,RANDBETWEEN(1,3))
Thanks for the advice and formula ?, formula with the COUNTIF function worked, so glad you've saved me from hours manually counting a long list of data.
Also, thanks for the extra 2 formulas ☺️, it'd be useful to use the formula which counts the total number of data.
But sorry of this is a stupid question, what does the third formula do with the INDEX function? When I copied and pasted it on my file just to figure it out, I don't quite understand what it was doing ? but I see that you applied this formula on you entire data column
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
i was just a little bit lazy, in B2:B39, i wanted them random filled with one of the 3 options.
So in total, there were 38 items but random divided over the 3 options.
Each time excel recalculates, that is redivided ... .
So nothing to worry about, in your sheet you fill the 3 categories yourself, not with that formula.
 

Nellycorn

New Member
Joined
Mar 14, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
i was just a little bit lazy, in B2:B39, i wanted them random filled with one of the 3 options.
So in total, there were 38 items but random divided over the 3 options.
Each time excel recalculates, that is redivided ... .
So nothing to worry about, in your sheet you fill the 3 categories yourself, not with that formula.
Oooh I see?. Again, thank you very much for the help ☺️
 

Forum statistics

Threads
1,176,105
Messages
5,901,407
Members
434,891
Latest member
mahmoud Alhamaky

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top