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

1. ## 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.  Register To Reply

2. ## 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"  Register To Reply

3. ## 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!  Register To Reply

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

Second attachment showing desired end result:  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

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

Thank you very much CK76.  Register To Reply