Format Display fractions to 64ths

Really simple for someone who took time to read the iWork Formulas and Functions User Guide available to all of us from thez Help menu.



In column A of the table "Tableau 2" are numerical values.

In column B I entered the formula:

=IF(ISERROR(VLOOKUP(A,fractions :: A,1,0)),"",VLOOKUP(A,fractions :: A,1,0))

I applied also the format fractions (two digits).
I was too lazy to apply it to column A of the table "fractions".

Yvan KOENIG (from FRANCE lundi 25 mai 2009 20:55:18)

I didn't urged you to use the Help but to read the "iWork Formulas and functions User Guide".

The delivered Guides are the unique source of information which I ever used upon iWork components.
From my point of view they are well done and really efficient.
Of course we must read carefully but isn't it the basis ?

In my formula I use three functions.
These three ones are perfectly described whith some examples.

I apologize, as a French user I am able to read the English guide too, but I can't learn the way to read English for you.

VLOOKUP
The VLOOKUP function returns a value from a range of columns by using the left
column of values to pick a row and a column number to pick a column in that row.
VLOOKUP(search-for, columns-range, return-column, close-match)
ÂÂ search-for: The value to find. search-value can contain any value type.
ÂÂ columns-range: A range of cells. range is a reference to a single range of cells,
which may contain values of any type.
ÂÂ return-column: A number that specifies the relative column number of the cell
from which to return the value. return-column is a number value. The leftmost
column in the range is column 1.
ÂÂ close-match: An optional value that determines whether an exact match is
required.
close match (TRUE, 1, or omitted): If there’s no exact match, select the column with
the largest top-row value that is less than the search value. Wildcards can’t be used
in search-for.
exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can
be used in search-for.
Usage Notes
ÂÂ VLOOKUP compares a search value to the values in the leftmost column of a
specified range. Unless an exact match is required, the row containing the largest
left-column value that is less than the search value is selected. Then, the value from
the specified column in that row is returned by the function. If an exact match
is required and none of the leftmost-column values match the search value, the
function returns an error.
Examples
Given the following table:
=VLOOKUP(20, B2:E6, 2) returns E.
=VLOOKUP(21, B2:E6, 2) returns E.
=VLOOKUP(“M”, C2:E6, 2) returns dolor.
=VLOOKUP(“blandit”, D2:E6, 2) returns 5.
=VLOOKUP(21, B2:E6, 2, FALSE) returns an error because no value in the left column exactly matches
21.

ISERROR
The ISERROR function returns TRUE if a given expression evaluates to an error and
FALSE otherwise.
ISERROR(any-expression)
ÂÂ any-expression: An expression to be tested. any-expression can contain any value
type.
Usage Notes
ÂÂ It is often better to use the IFERROR function. The IFERROR function provides all the
functionality of ISERROR, but allows for trapping, not just identifying, the error.
Examples
If B1 is a number value and D1 evaluates to 0, then
=IF(ISERROR(B1/D1),0,B1/D1) returns 0 since division by zero results in an error.
=IFERROR(B1/D1,0) is equivalent to the previous example, but requires only one function.

IF
The IF function returns one of two values depending on whether a specified
expression evaluates to a Boolean value of TRUE or FALSE.
IF(if-expression, if-true, if-false)
ÂÂ if-expression: A logical expression. if-expression can contain anything as long as the
expression can be evaluated as a Boolean. If the expression evaluates to a number, 0
is considered to be FALSE, and any other number is considered to be TRUE.
ÂÂ if-true: The value returned if the expression is TRUE. if-true can contain any value
type. If omitted (comma but no value), IF will return 0.
ÂÂ if-false: An optional argument specifying the value returned if the expression is
FALSE. if-false can contain any value type. If omitted (comma but no value), IF will
return 0. If entirely omitted (no comma after if-false) and if-expression evaluates to
FALSE, IF will return FALSE.
Usage Notes
ÂÂ If the Boolean value of if-expression is TRUE, the function returns the if-true
expression; otherwise it returns the if-false expression.
ÂÂ Both if-true and if-false can contain additional IF functions (nested IF functions).
Examples
=IF(A5>=0, “Nonnegative”, “Negative”) returns the text “Nonnegative” if cell A5 contains a number
greater than or equal to zero or a nonnumeric value. If cell A5 contains a value less than 0, the
function returns “Negative”.
=IF(IFERROR(OR(ISEVEN(B4B5),ISODD(B4B5), FALSE),), “All numbers”, “Not all numbers”) returns the
text “All numbers” if both cells B4 and B5 contain numbers; otherwise the text “Not all numbers.” This
is accomplished by testing to see if the sum of the two cells is either even or odd. If the cell is not a
number, the EVEN and ODD functions will return an error and the IFERROR function will return FALSE;
otherwise it will return TRUE since either EVEN or ODD is TRUE. So if either B4 or B5 is not a number
or Boolean, the IF statement will return the if-false expression, “Not all numbers”; otherwise it will
return the if-true expression “All numbers.”

Yvan KOENIG (from FRANCE lundi 25 mai 2009 23:05:34)

Neil,

I don't think the help file or the Functions guide would have gotten you to this solution.

If you are relatively new to spreadsheets (as you say you are) then, yes, it is a lot to learn. For those moving from Excel, all they have to learn is the interface and how Numbers differs from Excel. The solution provided by Yvan for your fraction display question is very likely the same way you would do it in Excel.

Badunit wrote:
Neil,

I don't think the help file or the Functions guide would have gotten you to this solution.

I apologize but

But what I want to do is have a cell show a blank when the fraction is not an exact fraction of 1/2, 1/4/,1/8/, 1/16, 1/32 or 1/64.

is exactly what the VLOOKUP function does.
Using only it returns red triangle when there is no exact match.
I used the IF function and the ISERROR one to replace these reds triangles by blank cells.

I repeat that all these features are perfectly described in the iWork Formulas and functions User Guide.

Yvan KOENIG (from FRANCE mardi 26 mai 2009 15:02:53)

Hi Neil,
As Yvan stated those functions are all in the users guide. Though he's correct you must know what to look for. One could say "hey there's a widget", but, if you don't know what is a widget you won't see it.

From lack of spreadsheet knowledge don't beat yourself up. Go purchase the Dummy's book on spreadsheets such as Excel. My home library contains Que Excel 97 and Que Lotus 1-2-3. By todays standard they're old however, the formulae and techniques are valid even with Numbers.

All spreadsheets basically are the same. Each does things differently each has different whistles and bells; same as driving different cars. Badunit stated "moving from Excel, all they have to learn is the interface and how Numbers differs from Excel". However, as you stated your knowledge with Excel is limited, you lacked the point of reference.

Please remember, don't tell a client you can or will do something you haven't the knowledge for. A coworker was playing with (learning) MS Access. The boss noticed it then asked if he would build a simple database for him. The guy was excited to try so he said yes. Simple became complicated (more features). Quickly he was way over his head and sadly he couldn't deliver (his knowledge was weak, he could spell MS Access). His boss became increasingly frustrated about this guys lack of Access ability he fired him for incompetence. If he had stayed with what he knew he'd still be there.

Cordially,
RicD