+ Reply to Thread
Results 1 to 3 of 3

Understanding the syntax of certain formulas

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    Bakersfield
    MS-Off Ver
    Excel 2011
    Posts
    1

    Understanding the syntax of certain formulas

    Hi, I am new to the forum. Although I have used Excel for years I am pretty lame. The forum has already helped in figuring out some jobs I had to do for my bosses—they think I know Excel (it is sobering to realize how much I have deceived them).

    On an immediate note: Below are some formulas that I know work, but do not know certain things in each.

    =LEFT(D13,5)
    - I understand D13, but what does "=LEFT" and the ",5" mean?

    =HOUR(H13)/24+CEILING(MINUTE(H13),15)/(24*60)
    - OK, H13 is the cell with a time, but what is "CEILING"?
    - MINUTE(H13) is going to incumentalize (I made up this word) an hour into 15 minutes with the ",15" right? But what is the 24*60 doing to the whole equation?

    =RIGHT(D13,2)
    - Again, what is the ",2" mean?

    =TEXT(F13,"ddd")
    - Am I right in saying the "=TEXT" is going to convert the F13 date into text?

    Thanks guys and gals for your welcome and any help you can give!

    Jeff

  2. #2
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    227

    Re: I'm new and need! =HOUR(H13)/24+CEILING(MINUTE(H13),15)/(24*60)

    Welcome to the forum! My comments are in red below.

    =LEFT(D13,5)
    - I understand D13, but what does "=LEFT" and the ",5" mean?
    the 5, or the number that comes after the comma, refers to the number of characters. In this example, this formula would return the first 5 characters from cell D13


    =HOUR(H13)/24+CEILING(MINUTE(H13),15)/(24*60)
    - OK, H13 is the cell with a time, but what is "CEILING"?
    - MINUTE(H13) is going to incumentalize (I made up this word) an hour into 15 minutes with the ",15" right? But what is the 24*60 doing to the whole equation?
    I've never used the ceiling function, so I can't give a good explanation. Maybe somebody else could. Through google, I found out that it rounds the result. Diving by 24*60 sounds like you're trying to divide it by the number of minutes in a day.

    =RIGHT(D13,2)
    - Again, what is the ",2" mean?
    the 2, or the number that comes after the comma, refers to the number of characters. In this example, this formula would return the last 2 characters from cell D13

    =TEXT(F13,"ddd")
    - Am I right in saying the "=TEXT" is going to convert the F13 date into text?
    check out this website: http://www.techonthenet.com/excel/formulas/text.php.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,179

    Re: Understanding the syntax of certain formulas

    Welcome to the Forum! From one Jeff to another:

    First, all your questions about how the functions work are explained in Excel Help. You can also double-click on a cell with the formula, and then click on the name of the function in the pop-up to see the help page for it.

    =LEFT(D13,5)
    - I understand D13, but what does "=LEFT" and the ",5" mean?
    LEFT is a function that returns the leftmost characters in a string. The 5 tells how many characters to return. For example

    =HOUR(H13)/24+CEILING(MINUTE(H13),15)/(24*60)
    - OK, H13 is the cell with a time, but what is "CEILING"?
    CEILING returns the lowest integer that is greater than or equal to the value. For example
    =CEILING(123.4) returns 124

    - MINUTE(H13) is going to incumentalize (I made up this word) an hour into 15 minutes with the ",15" right? But what is the 24*60 doing to the whole equation?
    Excel stores dates and times in units of days. That is, it stores one day as 1.0. It stores 12 hours as 1/2 of a day or 0.5. It stores 1 hour as 1/24 of a day. So taking a number of hours and dividing by 24 gives the correct number to store in Excel as a date/time (you didn't ask about the hours, but there ya go). Dividing minutes by 24*60 (the number of minutes in a day) also gives the correct number to store in Excel as a date/time.

    =RIGHT(D13,2)
    - Again, what is the ",2" mean?
    Again, the same thing as LEFT.

    =TEXT(F13,"ddd")
    - Am I right in saying the "=TEXT" is going to convert the F13 date into text?
    Almost. It converts the date in F13 to text in the form of the day of the week abbreviated to three letters. So if F13 has "8/7/2013", the result will be "Wed".
    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

+ 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. understanding syntax
    By derwalroszsagt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2012, 01:15 PM
  2. [SOLVED] Excel 2007 : Match Syntax - Verify My Understanding
    By CatherineCarey in forum Excel General
    Replies: 2
    Last Post: 04-19-2012, 03:10 PM
  3. Understanding Step - 1 syntax in for loop. VBA
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2012, 10:34 AM
  4. Replies: 2
    Last Post: 06-24-2010, 11:44 PM
  5. Understanding syntax to average values
    By bubba57 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-26-2009, 11:37 AM

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