Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Daily Dose of Excel » All Tags » Formulas (RSS)

Browse by Tags

  • Extract the last token in a cell

    There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that. The example we will use is the following. Cell A1 contains the value c:\x\yz.jpg. What [...] Read More...
    Filed under: ,

  • Formula Challenge

    I don’t know how to comment on Chandoo’s site, so I’ll post it here. I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help me with a formula for this? Here’s my contribution. A bit long. =IF(ABS(LARGE(A1:C1,2)-MAX(A1:C1))<abs (LARGE(A1:C1,2)-MIN(A1:C1)),AVERAGE(LARGE(A1:C1,2),MAX(A1:C1)),AVERAGE(LARGE(A1:C1,2),MIN(A1:C1))) I figured [...] Read More...
    Filed under:

  • FORECAST Doesn’t Like Dates

    I’ve been working with the FORECAST worksheet function a little lately and it’s been a struggle. First a quick look at the function FORECAST(x,known_y's,known_x's) The known x’s and known y’s form a trendline based on linear regression, and the function returns the y coordinate on that line for the provided x. The worksheet function seems to handle [...] Read More...

  • Formula Editing Made Easier

    I’ve been working a bit more with editing formulas in Notepad++. I changed my xml file names to be different than Bob’s. I’m unable to Remove a user defined language without causing errors and having to reinstall Notepad++. The two files I have are XLFF.xml - goes in ..\Program Files\Notepad++\plugins\APIs\ xlf_udl.xml - import from [...] Read More...
    Filed under:

  • Formula Editor in Notepad++

    Bob wrote a blog post last week, Formulas Made Easy, that I read with great interest. He uses a custom language in Notepad++ to allow for indenting formulas, editing formulas, and all the other stuff you can do in a text editor that you can’t do in Excel’s formula bar. I downloaded Notepad++ [...] Read More...
    Filed under:

  • Case Sensitive Lookup

    The VLOOKUP function (and its brothers MATCH, LOOKUP, and HLOOKUP) do not care about the case of the lookup value. Take this data, for example: A B 1 Name Number 2 Bob one 3 Jim two 4 bob three 5 jim four The formula =VLOOKUP("bob",A2:B5,2,FALSE) will return “one”. It finds “Bob” and quits looking because it considers that a match. If you need to find the true “bob”, you can use [...] Read More...
    Filed under: ,

  • Good Row Bad Row

    In the office, I sometimes deal with a table of values. In that table, there are good rows, and bad rows. I want to quickly put the good rows to the top of the table, and leave the bad rows at the bottom of the table. So I take the first unused column, give it a rubbish [...] Read More...

  • Formula Tips

    Al sent me a formula to revise. Before: =IF($B11<>0,IF(OR(ISNUMBER(FIND("-",J11))=TRUE,ISNUMBER(FIND(" ",J11))=TRUE,ISNUMBER(FIND("_",J11))=TRUE), IF(ISNUMBER(FIND("-",J11))=TRUE,TRIM(SUBSTITUTE(J11,"-","")),IF(ISNUMBER(FIND(" ",J11))=TRUE,TRIM(SUBSTITUTE(J11,"  ","")),IF(ISNUMBER(FIND("_",J11))=TRUE,TRIM(SUBSTITUTE(J11,"_","")),TRIM(J11)))),TRIM(J11)),"") After: =IF($B1<>0...

  • Cash Advances on Credit Cards

    I had the unfortunate opportunity to learn how cash advances on credit cards work. The particular card I saw was from Capital One, but I think they all work the same. There are three important things to know about cash advances: The interest rate is huge - usually in the mid-20% neighborhood Interest starts on [...] Read More...
    Filed under:

  • Multiple Substitute Formula

    MaryAnn asks an interesting question. In column A there is text that may or may not contain the name of a US state. In column B there is a list of US states. In column C, we want the column A text without the state names. I think this would be a pretty [...] Read More...
    Filed under: ,

  • Formula Tokenizer

    A few years ago, I wrote a Formula Formatter add-in to present a long formula for easier reading. It does it through a process called Tokenizing, which is basically a process of putting the components of a formula into labelled boxes. That add-in was compatible with Excel 2003 formulas, but Excel 2007 brought us extra formulas [...] Read More...

  • Golf Scores

    A couple of my golf buddies and I were debating the handicaps of a course we regularly play. Each hole of a course is handicapped 1 through 18 with 1 being the hardest hole and 18 the easiest. Handicaps are used to allow golfers of differing abilities to complete equitably. Because my [...] Read More...
    Filed under: ,

  • External Links

    Ron sez: What happens often is that one person will have one of these spreadsheets open and the other spreadsheets linked to the subject spreadsheet closed and decide to insert a column that throws off the references of the closed linked spreadsheets. If the person knew which spreadsheets were linked to it, the person could open [...] Read More...
    Filed under: ,
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.