This was cool. Thanks. I have a few additional questions if can you entertain them, please. How do I set up the Pareto chart with multiple columns? So I have customers as my rows and 12 types of complaints as my columns. I need to be able to figure out with types are my top 80% issues. Moreover, I need to be able to filter that to different customers as well.
Hi Sumit, Really appreciate your videos... they are awesome and well explained... just a query all these videos are done on excel 2010 and so there are lot of features which 2007 excel does not have... can you help me with this... when i learn work on excel 2007 .. unfortunately get stuck somewhere or the other ...please help
Great info. Question: Can a dynamic chart be created using pivot table information? I have a cost column along with its correlating cumulative %, but I cannot determine how to link the chart to the "dynamic" portion of this. Thank you in advance!
Thanks Sumit for such a useful and Knowledgeable video. However i have few queries:- 1. Why "1" is added manually in the match formula, is there any other formula which can give direct result? 2. At the end when you will select target as 100% this chart will give the blank, do you have any solution for that? 3. Can we use anything else in place of applying NA function? Could you please answer this.
hey mayank, 1. In match formula we used 'less than' logic so if you select target of 40% by this formula it will return only 35.2% which is not correct b'cause it won't achieve 40% target, to compensate this we added 1 so now it will give us 63.1% which is correct. 2.We added 1 to formula so when you select 100% the range goes out of selected range. I tried adding one dummy value of 100% below the cell containing 100% in cumulative % column and it worked. Try it. 3. You can try- "" . But when you write formula with NA() error it gives better understanding, it will only return blank cells without any value and may lead to confusion.
Using the current formula for the "Cumulative %" produces reference errors if you move the scrollbar to 100%; however, if you change it to "=IF($B$14=0,0,INDEX($C$2:$C$10,IFERROR(MATCH($B$13,$C$2:$C$10,1),0)+IF(COUNTIF($C$2:$C$10,$B$13),0,1)))" it works fine.
What about when scroll value is zero and 100 %? How about, =IF(C27=0,0,IF(C27=100,INDEX(D2:D22,IFERROR(MATCH($C$25,$D$2:$D$22,1),0)),INDEX(D2:D22,IFERROR(MATCH($C$25,$D$2:$D$22,1),0)+1)))
Thank you, you were clear as crystal in your explanation.
Brilliant. Just what i'm looking for concerns management. Thank you.
Clear and simple. Very helpful. Thank you.
Brilliant! Thank you so much.
Great work!
Thank you brother .it is very important for me
Thank you Sumit. Very nice video.
YOU ARE AMAZING!!!!
Fantastic work, very useful.
Excelent!!!! and very Useful, Thank you
Nice and very useful. Thxs.
Great video - thanks
A bit fast but I understand it very clear throughout the vid. Thanks
Extremely useful. Thanks a lot :-)
nicely explained, thanQ
Is there a way to make it dynamic when value of Column B keeps changing?
When selection is 100 %, Reference error comes. What shod we do in the code at B13?
This was cool. Thanks. I have a few additional questions if can you entertain them, please. How do I set up the Pareto chart with multiple columns? So I have customers as my rows and 12 types of complaints as my columns. I need to be able to figure out with types are my top 80% issues. Moreover, I need to be able to filter that to different customers as well.
Hi Sumit, Really appreciate your videos... they are awesome and well explained... just a query all these videos are done on excel 2010 and so there are lot of features which 2007 excel does not have... can you help me with this... when i learn work on excel 2007 .. unfortunately get stuck somewhere or the other ...please help
how to put this scroll
Great info. Question:
Can a dynamic chart be created using pivot table information? I have a cost column along with its correlating cumulative %, but I cannot determine how to link the chart to the "dynamic" portion of this. Thank you in advance!
We
At the end when you will select target as 100% this chart will give the blank, do you have any solution for that?
Dear Shrinivas please see my answer on Mayank Goyal's comment.
Great video!!!!!!
But I m getting reference error.
Thanks Sumit for such a useful and Knowledgeable video. However i have few queries:-
1. Why "1" is added manually in the match formula, is there any other formula which can give direct result?
2. At the end when you will select target as 100% this chart will give the blank, do you have any solution for that?
3. Can we use anything else in place of applying NA function?
Could you please answer this.
hey mayank,
1. In match formula we used 'less than' logic so if you select target of 40% by this formula it will return only 35.2% which is not correct b'cause it won't achieve 40% target, to compensate this we added 1 so now it will give us 63.1% which is correct.
2.We added 1 to formula so when you select 100% the range goes out of selected range. I tried adding one dummy value of 100% below the cell containing 100% in cumulative % column and it worked. Try it.
3. You can try- "" . But when you write formula with NA() error it gives better understanding, it will only return blank cells without any value and may lead to confusion.
how to put pareto data point in the left corner (0%) ?
Nice, very useful, supper
Excelent!!!
Using the current formula for the "Cumulative %" produces reference errors if you move the scrollbar to 100%; however, if you change it to "=IF($B$14=0,0,INDEX($C$2:$C$10,IFERROR(MATCH($B$13,$C$2:$C$10,1),0)+IF(COUNTIF($C$2:$C$10,$B$13),0,1)))" it works fine.
Is this formula for the "Cumulative %" column?
When I get to 100% it doesnt show the value.
Sir this is very complicated, please bring it easy way
What about when scroll value is zero and 100 %?
How about,
=IF(C27=0,0,IF(C27=100,INDEX(D2:D22,IFERROR(MATCH($C$25,$D$2:$D$22,1),0)),INDEX(D2:D22,IFERROR(MATCH($C$25,$D$2:$D$22,1),0)+1)))
Wouldn't the formula be much simpler if u used VLOOKUP instead?
TRUMP TRAIN!!!!!!!!!!!!!!!!!!!