what is {=TABLE(D11,D10)}

what is {=TABLE(D11,D10)} in the formula? i found it in a protected worksheet

there is a table where the formula show this.
0
Leung (8)
12/20/2004 4:23:03 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
264 Views

Similar Articles

[PageSpeed] 32

> .. {=TABLE(D11,D10)}
It's a formula (looks like an array formula)
which has to be set-up/invoked via the Data > Table menu
where D11, D10 are the row and column inputs
[Check up: "data table" in Help]

Perhaps a simple example?

Suppose we have an output cell of interest in D12
which depends on 2 upstream, precedent cells: D10 and D11

Let's say we have in D12: =D10*2+D11*5
(just a simple formula, but formula could be of any
complexity, as long as it depends on D10 and D11
upstream)

To see how the value in D12 would vary depending
on a range of possible inputs in D10 and D11
("what-ifs")
we could set up a 2 var data table:

Put in say, F12: =D12
(a simple link to the output cell of interest)

List across in G12:K12 the sample values: 1,2,3,4,5
(These are for the row input cell: D10)

List down in F13:F17 the sample values: 1,2,3,4,5
(For the col input cell: D11)

Select F12:K17

Click Data > Table

In the dialog, put for:
Row input cell: D10
Column input cell: D11
Click OK

You'll get the grid below which computes
in the intersecting range: G13:K17,
the calculated values of the output cell D12
based on the listed values of the 2 precedents, i.e.:
D10 (values listed in the "row": G12:K12), and
D11 (values listed in the "col": F13:F17)

120 1 2 3 4 5
1 7 9 11 13 15
2 12 14 16 18 20
3 17 19 21 23 25
4 22 24 26 28 30
5 27 29 31 33 35

(It's usual to mask the link cell F12 by
formatting the font in say, white
to blend-in with the fill color)

You could change the listed values in
either G12:K12 or F13:F17
and the table will recalc accordingly

Extend to suit ..

Note:
-------
The formula: {=TABLE(D10,D11)} will appear in G13:K17
Albeit it looks like an array formula, it cannot just
be entered as such.  The construct must be done / invoked
via the Data > Table steps outlined
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Leung" <Leung@discussions.microsoft.com> wrote in message
news:A0C17314-A4BD-4602-9A1E-50C901B94110@microsoft.com...
> what is {=TABLE(D11,D10)} in the formula? i found it in a protected
worksheet
>
> there is a table where the formula show this.


0
demechanik (4694)
12/20/2004 5:08:18 AM
Reply:

Similar Artilces:

Percentages in Pivot Tables
I have two pivot tables in my worksheet - one is calculating the % value correctly, one isn't. Basically the one that isn't is a pivot table summarising data from three files. The % from the 'Grand Total' row is actually adding up the percentage column, instead of dividing the two earlier columns. I don't think I am making myself too clear, but if there is someone who thinks they could help me and wouldn't mind looking at my spreadsheet, I would be really grateful as I am tearing my hair out! Is the % column coming from your source data? If so, instead of usin...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

Contract duplicates in current & historical tables
We seem to be having a problem, we have to 2 tables in , 1st table contains all current contracts, 2nd table contains all historical contracts. We are creating a reporting tool that queries first the current table, then the historical table in order to see all the revenue but our report generates duplicate contracts (duplicate revenue). We discovered that the same contract was in both tables, my question is has anyone experienced such a problem before and how do we fix this? If you are good at SQL, you could create a view of the two tables Joined with distinct records to eliminate the dup...

Pivot tables #39
How do I link 2 pivot tables together? ie. If I filter on one pivot table I would like the other pivot table to filter to the same criteria. All data is from the same source. thanks, Carrie I suggested the following, when you posted this question a few days ago: '============================ There's a sample file here that changes the page field in all pivot tables, when the master pivot table is changed: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'Change Page Field' '====================== Do you try this and have problems? car...

Multiple Consildation Ranges in a Pivot Table.
Hi, Once again a bit of help is required. I am using Excel 97 sr2. I have two worksheets with 5 columns in each. Both sheets have the same headings Date / Order Ref / Order Type / Container Ref / Container Type. There are approx 50,000 rows in each sheet. I am trying to consolidate these two tables into one pivot table. But I am only getting the following lables to drag an drop into place. Page / Row / Column / Count rather than the headings I would like Date, Order Ref etc. Am I doing something wrong? If you choose to use multiple consolidation ranges, you won't get the same pi...

Portion of a table will not print
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a table with about 30 rows and eight columns spanning two pages. The margins are set to 1in. or greater all around. On screen the table appears fine. However, the bottom of the first page of the table will not print. In print preview, the bottom quarter of the page appears with cells outlined but with no cell content. When printing, that same portion that shows up with empty cells in the preview is below the bottom margin of the first printed page and does not flow onto the second page - it is not printed at a...

Pivot table, data from various spreadsheet
I have data in 3 separate spreadsheets (in the exact format). I want to have a pivot table that consolidate data from these sheets. Is this possible? Try Debra's page on: Excel -- Pivot Tables -- Multiple Consolidation Ranges at: http://www.contextures.com/xlPivot08.html -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Derrick" <anonymous@discussions.microsoft.com> wrote in message news:19d501c52b80$8e0b4a40$a601280a@phx.gbl... > I have data in 3 separate spreadsheets (in the exact > format). I want to h...

Pivot table for worksheet text fields?
Can a pivot table concatenate text that appears in a worksheet? I often use Excel to lay out questionnaire responses. Pivot tables are great to calculate and create cross-tabulations of data: Of those who answered Yes to Question 3, how many also answered No to Question 4? But I usually include open ended prompts in my questionnaire: Questions 4: If your answer to Question 3 is Yes, please explain why. Is there a way to use a Pivot table to concatenate (compile into one cell) all the text answers given in response to Question 4? If not, is there some other Excel function (...

Deleting rows from two tables
I have two tables both of which contain aprox 2000 rows and 3 columns. The headings in each column are repectively: tariff level, product code, product description. The problem is best illustrated with an example: Table 1 Tariff Level Product Code Product description 10% 0236 Animal fats 15% 4579 Industrial Chemicals 12% 2093 Tapes Table 2 Tariff Level Product Code Product Description 13% 1463 Meat 13% 4579 Compact disc's 1...

PM Report error A get/change first operation on table PM_Distribut
I am getting the following error while trying to print a history report in purchasing. A get/change first operation on table PK_Distribution_WORK_OPEN' (45). Yesterday another user's system hung while posting a marked AP batch. She ended up shutting down her computer, then we recovered the batch from my station and all appeared find. I am not sure if these are related. Plesae help. ...

Pivot Table question: Creating a new field that calculates the division of one data field by another
I have a pivot table that includes the sum of sales (a data field) and the count of sales (a data field) by product. It looks like this. sum of sales Count of units Product a 100 8 product b 45 6 Etc. I am trying to create another field that divides the sum of sales by the count of units for each product (i.e. in a fourth column. Is there an easy way to do this (within the pivot table). I tried using the calculated field option, but didn't seem to find the way to do it. Also once I have created a c...

CT Tables
I've several of tables loaded into my company database. I am looking at these tables and they have some interesting fields. Can anyone tell me what modules these tables are for? I cannot find any information on these tables in GP tables description. They all start with CT. CT00001 CT00002 CT00003 CT00004 CT00005 CT00102 CT00200 CT40401 Any help will be highly appreciated. Thanks, VJ These tables do not appear to be native to Great Plains, unless they are new to version 8. What third party products do you have installed along with Great Plains? Any developer worth his salt would ...

How can I paste a wide table from Excel into Quark?
With an old version of Excel I had no problem copying wide tables from Excel into Quark and then reducing them if necessary to fit the margins. Now I have New Microsoft Excel Worksheet and when I copy and paste a wide table, the right-hand columns do not appear in Quark. Please help. Maybe try my webtablemaker at http://www.geocities.com/excelmarksway It will convert your table to a html web page. From there, you could maybe import it into Quark if it has webpage import? - Mark "Tom Ireland" wrote: > With an old version of Excel I had no problem copying wide tables from...

refresh of pivot table clears fields (column headers)
Observation: Pivot report being refreshed on win XP clears Fields (column headers empty). Basic file works ok with excel 2002 - SP2 Problem occurs (only?) when same file is loaded & pivot refreshed on excel 2002 SP3. As well when refreshed in Macro as manual refresh. ...

How 2 set table size?
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C4D0A7.3F66BCF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I was wondering if someone could point me in the right direction. I = need to create a table that is 5" x12" , contains 3 columns and 20 rows, = and I need to lock the cells so they do not expand. Here are my = questions: How do I specify the table size of 5" x 12"? How do lock the cell sizes? Thank you, Deb ------=_NextPart_000_0012_01C4D0A7.3F66BCF0 Content-Type: text/html; ...

Double Counting in Pivot Tables
I need help :( The data in my spreadsheet is organized as shown below with each numbe representing a column 1. Customer Name 2. Name of Customer's Company 3. Name of specific project with customer (for simplicity think Projec x, Project y...) 4. Internal Consultant assigned to specific engagement (Jim, Jane...) 5. Customer satisfaction rating with our company (Good, Very Good...) My problem is that I need to sort the data in various ways and I a running into a double counting issue. For example, three interna consultants may be assigned to Project x and the customer satisfactio r...

Pivot Table
I am trying to create a pivot table that has some 10k records/rows. The column being used in the pivot table has mostly unique items... I am getting the following error msg "A field in your source data has more unique items than can be used in a pivot table. Microsoft excel may not be able to create the pivot table or may create the pivot table without the data from this field" This started to occur once the record/row length (or I should say the unigue items) exceeded about 8005. Is there a way to overcome this / or increase the number of unique items allowed? Any help woul...

How to draw blue borders dynamically like pivot table?
Dear all, After Added a pivot table onto a sheet, Excel will draw blue borders of its column area, row area and data area. If you click one of the cells of the pivot table, the blue borders are showed automatically; if the pivot table is not focused, the borders disappear. Is there a way to implement similar effect by programming? Regards, George ...

Update One table from another table
I have to create a separate database for an outsource company where the records are sent to the 'home' database daily. I need to know if it's possible to update the outsource table from the home table once processing has been done. Is it possible to have and update query match primary keys and update the corresponding fields? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1 ...

Pivot Tables referring to external data query
In my excel file I am querying external data(an ODBC database) in one sheet. The query data amounts to over 10,000 rows. I am using a pivot table to summarize the query data. As my query data expands, the pivot table range doesn't expand. Is there a setting that enables the pivot table range to expands with the query data? When the query is imported, a name should be automatically created in Excel. You can use that as the pivot table source. To find the name: Select the sheet that contains the imported data Right-click on a cell in the imported data range Choose Data Range Pr...

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Data in my pivot table changes to zeros
I have created a pivot table using a dynamic named range as my source. The source data consists of 52,416 rows and 10 columns showing: Membership code Date Hour Day Week Month Term Year Checkouts Checkins Everything appears as expected when I go through Excel's PivotTabl wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I plac in the data area of the PT) always come up as zeros. The PT seems t have replaced all the correct data in each cell with a zero! In addition, I have to manually change the fields to "-Sum of-..." a opposed to the "-Count of-..." ...

Combining Tables
I apologize if this has been answered, but I cannot seem to create th correct search to find it! I have been racking my brain for two days and am about to give up. My problem is that I need to combine two tables. Table 1: Item Number, Item Name 101 Thing 102 Other Thing Table 2: Item Number, Price 102 $64.99 What I want to do is create a single table: Item Number Item Name Price 101 Thing 102 Other Thing $64.99 Any help is much appreciated! Jami -- Message posted from http://www.ExcelForum.com...

Comapring rows of a table to multple rows within the same table
Hi, I have the following data which I have been tasked with identifying a primary case for each customer. Unfortunately the data contains duplicate records such as Case 1 and 2. I would be extremely grateful if you could tell me how can I compare all the rows related to Customer 1 and flag a case as the primary case. CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB 1,1,New, Unassigned, RU, HK 2,1,New, Unassigned, RU, HK 3,1,In Progress, Normal Review, RU, HK 4,1,New, Unassigned, IN, UK 5,2,New, Assigned, JP, 6,2,In Progress, Special Review, BR, HK 7,2,Co...

Combo Box From Table/Query
I have a table of part numbers using input mask to create format 1234-56-7899. When I import the list via table or query to the combo box, the drop down list doesnt show the hyphens, just the 8 consecutive numbers? I have done this many times with no issues. What have I missed. Thanks in advance. -- Message posted via http://www.accessmonster.com On Fri, 19 Feb 2010 05:07:22 GMT, "Bruister via AccessMonster.com" <u50445@uwe> wrote: >I have a table of part numbers using input mask to create format 1234-56-7899. >When I import the list via table or query to...