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

Browse by Tags

  • Conditional Correlation

    The CORREL function is used to find the correlation between two arrays. Because CORREL ignores text, you can use an array formula as one of the arguments of CORREL to limit it to a subset of the data. To demonstrate, we’ll need some data. I fired up QuickSampleData to make fifty rows with a [...] Read More...
    Filed under:

  • CELL Worksheet Function

    I learned something today. I “knew” that omitting the optional reference argument from the CELL worksheet function meant that the cell that contained the worksheet function was used. Patrick McDonald correctly pointed out that it uses the last changed cell, not the host cell. In a new worksheet, type =CELL("width") in cell B3. [...] Read More...
    Filed under:

  • Clock Face Angles

    Financial RAD Developer quotes this interview question Please tell me what you would implement to tell me the angle between then hands on a watch face at any given time of the day. Here’s my worksheet function to convert time to angle =ABS(((MOD(HOUR(G2),12)/HoursPerDay*DegreesInCircle)+((DegreesInCircle/HoursPerDay)*MINUTE(G2)/MinutesPerHour))-DegreesInCircle/MinutesPerHour*MINUTE(G2)) And my VBA Function TimeAngle(dtInput As Date) As Double         Dim dHour As Double...
    Filed under: ,

  • Worksheet Function to Test if Range Is Sorted

    Did you ever need a worksheet function to determine if a range is sorted? Neither have I. But I’m all about answering questions that haven’t been asked. =SUMPRODUCT(--(A1:A10>=A2:A11))=--(A11<a10 ) The SUMPRODUCT part compares every cell in the range to the one below it. If any cell is greater than the one below it, it [...] 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...

  • VBA Rounding

    When comparing values from various systems, I’d sometimes notice $0.01 differences. I put the differences down to rounding, but it seemed that no matter which approach I took, the differences could not be reconciled. It’s one of those things that really bugged me, and I’d feel guilty if I spent too much time on the problem… after [...] Read More...

  • 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...
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.