+ Reply to Thread
Results 1 to 7 of 7

What is purpose of the "0" in this formula?

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    What is purpose of the "0" in this formula?

    I recently received some help on this excellent forum. The formula worked very well for me. But I am one of those people that want to know how things work, just to thoroughly understand it's function. This is no way meant any negative connotation to how I received the help. But many days passed and I marked my post solved. But after messing with the formula for other situations (it works VERY well). I am just curious what it does.

    The formula is:

    =IF(ISNUMBER(MATCH(TEXT(A2,"0"),Sheet2!A$2:A$19,0)),TEXT(A2,"0"),"")

    What is the function of the "0" in the portion of the formula after (in both cases) (TEXT(A2,"0")

    I understand the other sections of the formula. Does the "0" in this particular context mean - looking for content that is not text?

    I very appreciate any help.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: What is purpose of the "0" in this formula?

    "0" is just formatting applied.

    Text(A2,"0") will return text value, that represents Round(A2,0).

    Ex: Say A2 = 2.1
    Text(A2, "0") = "2"
    If A2 = 2.5
    Text(A2, "0") = "3"

    Edit: By changing number of 0 in text function's format argument... you can add leading zero. Any number larger than defined by number of zeros will display as whole number string.

    Ex: If A2 = 2.5, Text(A2, "00") = "03", If A2 = 200.5, Text(A2, "00") = "201"
    Last edited by CK76; 07-03-2020 at 01:35 PM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    Re: What is purpose of the "0" in this formula?

    Hello CK76.

    So in the attached spreadsheet, is the explanation you provided meant to "align" the numbers that exist on SHEET 2, with the exact match to it's corresponding number on SHEET 1?

    I am going to attach the original spreadsheet in one attachment. Then I am going to attach the intended results in a second attachment.

    I appreciate your help. Also, Robert A. Heinlein is one of my favorites!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    Re: What is purpose of the "0" in this formula?

    Second attachment showing desired end result:
    Attached Files Attached Files

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: What is purpose of the "0" in this formula?

    In this case, Text function is used/needed since Column A of Sheet2 is stored as text value (not numeric value), but A column on Sheet1 is stored as numeric value.

    To to match up Sheet1!A2 to list on Sheet2. You need to use Text function to convert A2 to text value.

    Without it, this formula construct will return no result.

    Alternate approach:
    =IFERROR(AGGREGATE(15,6,Sheet2!$A$2:$A$19/(Sheet2!$A$2:$A$19/A2=1),1),"")

    This uses forced conversion of number stored as text into actual numbers by using mathematical operator ( divide '/', multiply '*' etc).

    See attached.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: What is purpose of the "0" in this formula?

    Oh, just saw you posted desired result. Do you actually need the code replicated for each? It's bit of duplication of data.

    At any rate, if you want to use single formula construct across range. I would do something like....
    =IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW($A$1:$A$19)/(Sheet2!$A$1:$A$19/$A2=1),1)),"")

    Copy across and down.

    See attached, Sheet3.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-04-2020
    Location
    Georgia
    MS-Off Ver
    2019
    Posts
    8

    Re: What is purpose of the "0" in this formula?

    Thank you very much CK76.

+ 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. [SOLVED] Brief formula to convert time values to "shift1", "shift2", "shift3".
    By darekpawel in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-11-2019, 07:05 AM
  2. Purpose of "ALL" in powerpivot measure
    By stephme55 in forum Excel General
    Replies: 0
    Last Post: 07-15-2016, 01:18 PM
  3. What is the purpose of using "<>"&"" In a Formula?
    By andersonsma in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2016, 12:45 PM
  4. what is the purpose for "--" in the SumProduct(-- or Max(-- formula
    By Bigkx06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-27-2013, 02:40 PM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. what is the purpose of "run dialog" in excel 2007?
    By hariprof in forum Excel General
    Replies: 2
    Last Post: 02-25-2010, 05:05 AM
  7. Purpose of the "If Not Intersect" statement
    By ExcelQuestion in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2008, 02:22 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