Function Large

Large takes a "value set". This can be a range or, at least in recent version of Numbers, can be a set. To create a set, you need to use the curly brackets, not parentheses, as in this example:

LARGE({B2,G3,B4,K55,B6},2)

I see that Excel requires parentheses if the items are cell references but requires curly brackets if the items are numbers and I see no way to have a mix of the two in Excel. Numbers allows a mix of cell references and actual numbers, all using curly brackets. This function does not export/import to/from Excel when it contains a set of cell references. Numbers imports from Excel as an error if the Excel function had parentheses and cell references. Numbers exports to Excel by turning all the references into their values, which destroys the usefulness.

Hi AndersAxel,

LARGE shows a blue warning triangle if the range includes one or more blank cells.

With no blank cells, LARGE works whether the cells contain entered numbers or the results of formulas.

C =B*2

D2 =LARGE(C,1)

Remove blank cells from the range:

Regards,

Ian.

Hi Anders,

Can you provide a specific example? Screen shot would help.

The profile informtion attached to your post indicates your Mac is running OS X Mountainl Lion, which would suggest Numbers v2.x (Numbers '09). Are these correct?

Here's a table made in Numbers 3.6.2, runnning in OS X 10.11.x El Capitan.

Column A contains random values, calculated by RAND(), then copied and repasted using Paste Formula Results. This prevents the random values being recalculated, possibly after the LARGE() results have been calculates, producing a mismatch between the LARGE results and the (new) values calculated in part by the results of RAND.

Column B contains the formula ROUND((109−ROW()^1.5)÷A2,2)

This calculates a value based of two variables: the value calculated in 'this row' of column A, and a value calculated from the row in which the formula resides. The result is rounded to two places after the decimal,giving the results shown. As can be seen, the ROW position is more significant that the random value between 0 and 1 assigned by the Rand function, giving a list ins which the values are listed in ascending order when the table is sorted descending on column A.

Column C contains the formula LARGE(B,ROW()−1), filled down to row 10, which correctly lists the results of the formula in column B from largest to least large.

Resorting the tabe descending on column A produces a new set of values, which Large again lists in correct order from largest to smallest in column C.

YMMV. of course, which is the reason for the request for a screen shot of an example where the LARGE function fails (and a copy of the formula using LARGE that fails in these circumstances.

Regards,

Barry

As mentioned by SGIII, UNION.RANGES is another option. Unfortunately it, too, is not compatible with Excel. I am not sure there is way to make your formula importable/exportable other than rearranging the spreadsheet so you can use a range.

Hi Anders,

LARGE expects a range, not a list.

Those blue warning triangles disappear when the empty cells are filled with zeroes.

Regards,

Ian.

If you have cells that aren't next to each other in a single range you can use UNION.RANGES within LARGE.

=STÖRSTA(SAMMANSL.INTERVALL(FALSKT;B2;D2;F2);1)

=LARGE(UNION.RANGES(FALSE,B2,D2,F2),1)

SG

Excel worksheets are monolithic, consisting of thousands of rows and columns. Excel worksheets often contain several different "tables" all on the one worksheet. That is why you need to be able to select ranges to print in Excel. In Numbers, though, you create tables of finite size and you arrange them on sheets the way you want them to appear. What you see is what you get. It is based on the idea that you are creating your document to be shown just the way you made it. If your plan is to print only a piece of a table, some of your choices include:

1. Redesign your document so that piece of the table is in a different, separate table on a new sheet.
2. Create a new table on a new sheet that pulls its data from the cells of your main table. Design this table to be what you want to see printed.
3. Hide the other columns and rows until you are left with only what you want to see and print.

It is a different paradigm from Excel.

AndersAxel wrote:

it returns same error.

What error message do you see when you click a red warning triangle?

SG

Hi Anders,

"Formula contains digits outside proper intervall."

But all cells are zero, should be allowed

The formula in my K2 (and fill right) is wrong. I have given the LARGE function a list. It expects a range.

Formula in K3 (and fill right) gives the correct result,

=LARGE($B3:$J3,K$1)

but the blank cells cause a blue warning triangle:

Fill the blank cells with zeroes (row 4) and the blue warning triangles disappear:

=LARGE($B4:$J4,K$1)

So you mean that it differs from LARGE function that is used by Excel. I want to search for 12 largest values.

I don't know. I don't use Excel.

Add more columns and fill the formula to the right!

The $ symbols keep the range absolute (fixed).

Regards,

Ian.