+ Reply to Thread
Results 1 to 26 of 26

Problems putting together formulas that will give me data for pareto chart

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Problems putting together formulas that will give me data for pareto chart

    Hi

    See attached

    Trying to create a formula that will display in D2 the most common word in a list. I've actually done that with =INDEX(range,MODE(MATCH(range,range,0))). I've also found the formula that will count how many times this word in any order comes up with =COUNTIF(range,"*word*").

    Great

    Now i want to find the 2nd most frequent, 3rd most frequent... 7th 8th etc.

    And then do a count if for each

    And then count the "cost" of each one so i can sort via cost to then make a graph.

    in column D, E & F i have basically put what I am trying to achieve manually as i might not be making sense.

    Many many thanks! Really will help with a project
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    Do you have a list of keywords (wire, carriage, etc,) which you wish to find in the range?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Hi Glenn

    Yeah but i'm thinking I can't share this list, hence why i made a shorter mockup version attached.

    In the past when I've asked for help i attached mock-data and then replicated it onto the real thing.
    Do you need the real list?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    No. In your sample... do you know in advance that Wire, Carriage and hammer are the values that you want to look for, or do you need to derive those by formula/VBA?

  5. #5
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Oh, apologies, no.

    I will need to identify those values first in order, then count them then sum the 'total spent'

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    I was afraid you'd say that!! I strongly suspect this will be a nightmare.

  7. #7
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Really?

    I thought since I've already found the most common value and counted it. I can then find the second most common, third, etc and do the same?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    Repost your file complete with the formula that you used. I'm away for a while, but thought your sheet was formula-free.

  9. #9
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Column d & E have the formulae
    E not sure how to sum that
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    There are STILL no formulae in the sheet you posted!!

  11. #11
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    woops - thought i did
    Attached Files Attached Files

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    OK. Your formula in column D does NOT work. It returns "wire" NOT because it occurs 6 times in column A, but because the SINGLE commonest text entry is "wire" - which occurs 4 times. Replace all 4 instances of "wire" with a unique phrase containing wire...

    Not see what happens...

    So... I was afraid you'd say that!! I strongly suspect this will be a nightmare.
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    In your real data, how many "words" can there be in a cell - MAXIMUM?

    How many rows of data do you have?

    Are "Wire" and any other word (s) in the cell ALWAYS separated by a space (i.e. is wirecutter a possible word that you would want to count towards determining the mode in column D)?

  14. #14
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    edit in response to previous comments
    Last edited by ZMAFC94; 12-19-2019 at 08:43 AM.

  15. #15
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Sorry sorry sorry - i'm just learning about this list.

    Background:
    Its a report of what a business has bought over a year.
    Trying to understand how much, of what, we have bought and what we have spent.

    I was gonna then order by highest to lowest.

    In your real data, how many "words" can there be in a cell - MAXIMUM?
    edit:Could be up to 8 then the software ive exported this from could cut off longer descriptions as they are different
    How many rows of data do you have?
    654
    Are "Wire" and any other word (s) in the cell ALWAYS separated by a space (i.e. is wirecutter a possible word that you would want to count towards determining the mode in column D)?
    edit yes but i think the user could enter something slightly different for each order, as it could be a new item, entered by anyone in the business
    Last edited by ZMAFC94; 12-19-2019 at 09:12 AM.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    Are you allowed to use VBA for a solution? If so, I think I have been able to plagiarise a solution from the interweb.

  17. #17
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Macro is allowed - the only alternative we can think of is to go through every line item and categorise them. Which should have been done from the beginning, when we installed the system.... a time before i was born.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    OK. First step. (You will very quickly fnd out that I know sweet FA about VBA). So if you have any Q's, be prepared for a blank stare coming across the www from across the Irish Sea.

    Open. Enable macros. Click the big button.
    Attached Files Attached Files

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    If you know your way around VBA, here is the thread from which I shamelessly stole the code (a few minor adaptions were made using Trial-And-Error!!).

    https://www.mrexcel.com/board/thread...-words.726216/

  20. #20
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    This works, thank you - i think I'll attach the actual list so you can see what i'm dealing with
    Last edited by ZMAFC94; 12-19-2019 at 09:48 AM.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    Hahahaha. So I can tell already what your next Q is....

    "How can I get it to return only significant words?"

    Can you feel my blank stare, yet??

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    It'll almost certainly have to be done manually. The SUM is going to throw up issues, too as PPE will count £XX, as will refill, so choosing what is significant, and what is not, is probably totally down to professional judgement (yours!!).

  23. #23
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Yeah, ha! i was reserved to the conclusion that someone will have to categorise these because of all the unique values.

    If i can get these to 4 or 5 categories - then i should be ok to do a simple bar graph...

    Many thanks

  24. #24
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    And to get a total

    =SUMIF(A:A,"*"&G2&"*",B:B)

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Problems putting together formulas that will give me data for pareto chart

    Thanks again Glenn

    I recognise your name because you always help.

    Dunno how, but its always you

    even with hundreds of problems to solve

    Cheers

  26. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Problems putting together formulas that will give me data for pareto chart

    What you're really saying is that I need to get a life!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pareto Chart
    By Cessh in forum Excel General
    Replies: 1
    Last Post: 10-24-2016, 02:11 PM
  2. [SOLVED] Pareto Chart
    By lyla22 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 09-17-2014, 05:57 AM
  3. Pareto Chart without altering data
    By perksaus in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-10-2013, 06:54 PM
  4. Replies: 2
    Last Post: 06-27-2011, 10:47 PM
  5. Pareto chart
    By arnab0711 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-06-2010, 02:08 PM
  6. Putting data/using formulas on an image
    By AKL in forum Excel General
    Replies: 1
    Last Post: 07-08-2009, 10:05 AM
  7. Pareto Chart, set line chart origin at zero
    By ExcelFed in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-03-2008, 05:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1