Numbers Functions/Formulas Help

Worldwide, you can come to this community.

On your Mac (and in Numbers), yo can type an = into any cell of a Number Tanlel to open the Function Browser.

The Browser contains a list of all the functions supported in your version of Numbers, sorted into categories. There's a brief description of what each function does, a line showing the syntax of that specific function, and at least one example of how to use that function in a Numbers document.

In the Help menu, you'll also find a link to the User Guide for Numbers—worth reading for an extensive introduction to how things work in Numbers.

Also, scan the available Templates provided with Numbers. If the subject of a template is something you mightuse, open a document using that template, and take a look under the hood. Most, as well as making useful documents, can also serve as a tutorial for the functions used. Of particular use, if you're planing to use charts (graphs) in some of your Numbers documents, is the Charting Basics template.

There's no need to keep these documents 'forever'—If you're using them just to learn, you can delete them as soon as you have finished with them.

And for hepl is creating your Numbers document, when you hit a brricade, did I mention you could ask for help here in the Numbers for Mac Community?

Regards,

Barry

Help > Formulas and Functions Help in your menu is a great resource, with lots of examples.

Also doing a web search for a particular function can help. Most results will be about Excel. But don't let that deter you. Most Excel functions work the same way in Numbers.

SG

The difficulty here is that there is no function that will do the search directly for a value that is "within ±10% of a target.

MATCH, which is the search functionI used here, is capable of finding the largest value less than or equal to the search value, or of finding the smallest value more than or equal to the search value, or of requiring an exact match of the search value.

A formula using this talent of MATCH, and adding INDEX to return the matching percentage, and a table containing two auxiliary columns to show the upper and lower limits of the values acceptable to meet the ±10% requirement can be constructed as shown in the example below:

The formula is entered as shown in cell B2 of Table 2, then filled down to the last cell in that column.

IFERROR(IF(INDEX(Table 1::A,MATCH(A2,Table 1::C,1),)=INDEX(Table 1::A,MATCH(A2,Table 1::D,−1)),INDEX(Table 1::A,MATCH(A2,Table 1::B,1),),""),"n/a")

The formula uses this pair of functions, MATCH and INDEX to look up a percentage in Table 1 twice to determine if there is a match that is within the ±10% range. If the first search (of column C for a number less than or equal to the value being searched) and the second (of column D for a number greater than or equal to the value being searched) both return the same row number (and consequently the same percentage value, IF repeats the first search and returns the value it finds.

If the two Match and INDEX searches return different values, the comaparison returns FALSE, and IF returns a null string, making the cell containing the formula appear blank.

The whole formula (bold part) returns an error if either MATCH returns a 'can't Find error. This error message is caught by IFERROR, which returns the "n/a" text string seen in the bottom rows of column B of Table 2.

I would leave this (not bolded) part out while constructing and testing the formula (bold part) until you have that part error free (except fo the expected error which will occur in the cells showing "n/a" in the example.

You can find details regarding the three functions used in the Function Browser.

Regards,

Barry