+ Reply to Thread
Results 1 to 13 of 13

How to solve an excel problem?

  1. #1
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Question How to solve an excel problem?

    Now, theres an open-ended question, if ever there was 1? (how long is a piece of string? I hear you say?)

    Im sure we have all - at 1 point or another - been asked how we go about solving an excel problem, and have probably offered some advice on this?

    I thought it might be of use to have a thread devoted to just this question, and have members share their approaches? (maybe even make it a sticky?)

    This was my attempt on a thread I was answering...

    My approach is to determine which pieces of info I need to resolve the question.
    Then I (try and) figure out how to get each of those pieces of the puzzle (that is often the hard part)
    I then fit each of the peices together into 1 formula - and then test

    I guess what it comes down to is that you generally need two things (at least) to run a calc, but often, those 2 (or more) things are not inthe same format/context, so you need to convert 1 or the other - or sometimes both - to something similar that you can then compare or add or whatever.
    Once I combined the 3 pieces of info on sheet 1, that gave me a starting point

    There is no 1 standard 1-way-fits-all approach for this (at least, I dont thing so)

    Thoughts???
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: How to solve an excel problem?

    Organise, Analyse, Present.

    Or, define + establish data, define + execute rules / logic, define + format output.

    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to solve an excel problem?

    This is a good idea, Ford, and thank you for sharing!
    Here's usually what I go through

    For formulas:
    1) Make sure all sheet references, cell references, and named range references are not only valid, but pointed to the correct locations
    2) Walk through formula logic making edits/corrections where necessary
    3) Consider various functions that perform essentially the same effect (for example, vlookup vs index/match, countif vs isnumber/match, sumif vs sumproduct, etc) and get anything working with the correct outcome
    4) Make use of alternate functions in an attempt to simply/shorten, while doing my best to avoid volatile functions as well as avoid the need to array-enter a formula

    For VBA:
    1) Require variable declaration and try to run the macro to see if there are any mispelled or undeclared variables
    2) Declare anything that is missing, correct spelling mistakes
    3) Correct indentation if needed, add in any missing "End With", "End If", "Next" etc statements. Alternately, remove any extras that might exist
    4) Verify all workbook (if any) references, sheet references, and range references are valid and pointed to the correct location
    5) Walk through macro logic, apply editing and corrections where needed
    6) Attempt to simplify if possible (for example: multiple ElseIf can usually be simplified into a Select Case, there is almost always an alternative to using a loop, etc)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to solve an excel problem?

    My first thought as I read your attempt: It is kind of like solving story problems back in high school math classes. Which naturally reminded me of the Far Side cartoon where Gary Larsen depicts hell as a bookcase full of books with titles like "Story Problems" "More Story Problems" "Giant Book of Story Problems" and so on. But, in many ways I think there is some similarity. As you say, part of solving a generic Excel problem is to identify the information you are given, identify the quantity or quantities being requested, and trying to ascertain from the problem the logic/math steps that will go from the givens to the desired result.

    In many ways, I also see it as a "programming" problem -- considering a spreadsheet as a kind of programming language. In this way, the idea is to identify the "inputs", identify the desired "outputs", and then figure out the "processing" steps in between to get from the inputs to the outputs. Naturally, the more familiar I am with the available "commands" (aka functions) and the syntax for making them work, the easier it is to use them to go from input to output.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to solve an excel problem?

    Thanks for the inputs guys, already some really great approaches

    MrShorty, very valid comment...
    Naturally, the more familiar I am with the available "commands" (aka functions) and the syntax for making them work, the easier it is to use them to go from input to output.
    If you dont know what tools there are, how can you know what tool to use?

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: How to solve an excel problem?

    The function explorer and object browser are my friends.

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to solve an excel problem?

    My best success usually comes from figuring out how to ask myself or the internet the most detailed possible question in the least Excel-like terms.

    I do a lot of form design around the office and I prefer to have people describe what they have, what they want, and to do it in a way that a child might understand. I find working abstractly helps me formulate the logic of what can be, as opposed to how they think it should be accomplished.

    Many times while writing new threads, I have solved riddles of my own by describing the issue with such clarity that the question leaves no room for misinterpretation, and then I finally understand what I have, what I want, and the path of least resistance it would take to get there.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  8. #8
    Registered User
    Join Date
    02-18-2014
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to solve an excel problem?

    I have nothing to actually offer in terms of solving an excel formula but thanks Ford!!! This is exactly the kind of information that I was requesting and asking about.

    Would someone be interested in creating a good formula building problem/example (start with commonly used formulas) and running through your thought process at each step? Just an idea but it would be helpful for someone trying to learn how to build formulas.

    Thanks Everyone!!

  9. #9
    Registered User
    Join Date
    02-18-2014
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to solve an excel problem?

    ALSO....

    If there was a universal formula for building formulas what would it be....?

    I'm not great with excel functions so this is my uneducated guess:
    1. Ask yourself what is the goal
    2. Identify key pieces/cells to obtain goal
    3. Identify possible functions
    4. Test
    5. Refine

    I'm interested to know what your steps would be. I understand I'll learn the more I use it but I want to build up from a solid foundation using the best methods.

    Thanks

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to solve an excel problem?

    dive in ,rack brain,google ,try a few things then try some more is about it!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How to solve an excel problem?

    I usually start with:

    Is it more efficient to pull data: - Then Formulas
    Is it more efficient to push data: - Then Visual Basic code

    Please Login or Register  to view this content.
    And on and on. Is it text manipulation? Is it time and date related? There's a lot of families of formulas. Knowing the family that the question is related to helps narrow down the potential solutions.

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to solve an excel problem?

    Quote Originally Posted by pncarlson View Post
    I'm interested to know what your steps would be. I understand I'll learn the more I use it but I want to build up from a solid foundation using the best methods.
    Thanks
    Very good approach!

    I think it is time to discuss how to solve a specific excel problem.

    Below is my approach with my personal experience. For example:

    How to get numbers from alphanumeric string?

    A1 = "abc1234def" ==> 1234


    Step 1: what is the goal? get number from string: 1234 only

    Step 2: Analizing issue: find position of the first number (by vision it is 4th)

    Step 3: Which function may concerned: SEARCH(FIND),MATCH, LOOKUP, INDEX,...

    Step 4: Test and refine: searching each number from 0 to 9 in string to find if it exist:

    Normally:

    SEARCH(0,A1)
    SEARCH(1,A1)
    ...
    SEARCH(9,A1)

    but combining in one array formula:

    =SEARCH({0,1,2,3,4,5,6,7,8,9},A1)

    Enter.

    Very important tip: F2, then using F9 to evaluate the formula: {#VALUE!,4,5,6,7,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

    that says: 0 not found, 1 in 4th position, 2 in fifth,...

    Now issue raising: to eliminate #VALUE in order to use MIN function: change A1 to A1&"0123456789", to ensure all

    matched.

    =SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"), says {11,4,5,6,7,16,17,18,19,20}

    the earliest, the smallest is 4th position, use MIN to get:

    =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")), returns 4.

    Now thinking of MID to get sub-string inside string: MID(A1,4,n) which 4 is MIN, n is length of sub-string.

    Also thinking of getting array: {1,12,123,1234,1234d,1234de,...} with n runs from 1 to 100 (100 or any number that large enough)

    =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(1:100))
    in which ROW(1:100) establish {1;2;...;100}, sign "--" to convert text-number to number

    Using F9, what we have so far :={1;12;123;1234;#VALUE!;#VALUE!;...; #VALUE!}

    the expected value is always the biggest in range of value. This range is accending , so LOOKUP can help to get the last value (farest value to the right):

    =LOOKUP(very-big-enough number, accending_range)

    =LOOKUP(10^10,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(1:100)))

    this is array formula, confirmed by Ctrl-shift-enter.

    = 1234

    It is sure that there are other solutions / approachs which may be more reasonable or shorter. Can not waiting to hear from all you guys.

    By the way, sorry for my terrible English. I have been using it for 2 years.
    Last edited by bebo021999; 03-06-2014 at 11:16 PM.
    Quang PT

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to solve an excel problem?

    VBA for bebo:
    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ 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. Replies: 1
    Last Post: 11-07-2013, 07:12 PM
  2. Determine values of parameters that will give a desired result (Solver?)"
    By gmazz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2013, 07:12 PM
  3. [SOLVED] Excel parsing data problem $100 dollars to whoever can solve this
    By velocitygraphix in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 05-08-2013, 07:36 PM
  4. [SOLVED] Can Excel solve my problem or do I need Access / Python?
    By MHT888 in forum Excel General
    Replies: 11
    Last Post: 01-13-2013, 02:54 PM
  5. Help! New to excel, need to solve a problem
    By xsd87x in forum Excel General
    Replies: 3
    Last Post: 04-18-2006, 11:55 PM

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