how to create a two-variable table?

Question

how to create a two-variable table?

hi there,

just bought Numbers for my IPAD.

usually when I work on MSFT XLS I do lots of two-variable data tables.

Is it possible on Numbers? and How?

thanks

iPad 2, iOS 4.3.3

Posted on Jul 12, 2011 7:25 AM

Reply

Answer 1

Jul 12, 2011 4:29 PM in response to davidefromrobella

David,

This is the Mac iWork discussion area. The iOS iWork discussions are here: https://discussions.apple.com/community/app_store/iwork_for_ios

I'm curious though. Why would a multi-variable table be any more difficult or different in Numbers? Is there some set of functions that you invoke with this table that you think might be missing? If so, what are they?

Jerry

Reply

Answer 2

Jul 13, 2011 2:02 AM in response to Jerrold Green1

jerry thanks for the reply,

in XLS a two-variable table is a tool to make scenario analysis, so basically the table is filled by scenarios by changing 2 variables output_cell= f (x,y) and you can see sensitivities on table as table row is x1,x2,x3 and table columns is y1, y2, y3..

this is an example:

http://www.dummies.com/how-to/content/how-to-create-a-twovariable-data-table-in- excel-21.html

Reply

Answer 3

KOENIG Yvan

Level 8

41,819 points

Jul 13, 2011 3:24 AM in response to davidefromrobella

As it's often the case, knowing what is really wanted help to give an accurate answer.

I hope that this one is.

In cell B5, the formula is =$B$7

In cell B7, the formula is :

=VLOOKUP($B$3,$B$8:$F$17,1+MATCH($B$4,$C$7:$F$7),0)

In cell C8, the formula is :

=$B$2*(1+$B8-C$7)

Appli FillDown and FillToRight to fill the table.

In B3 is a stepper (or a slider) (I'm not sure of which is what in English)

min value 0.01

max value 0.055

increment 0.005

percent

1 decimal digit

In B4 is a stepper (or a slider)

min value 0.10

max value 0.25

increment 0.05

percent

0 decimal digit

Yvan KOENIG (VALLAURIS, France) mercredi 13 juillet 2011 12:24:15

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Reply

Answer 4

Jul 13, 2011 4:23 AM in response to KOENIG Yvan

thank you Yvan,

but my real issue relates to the fact that variables might not be explicit..

YE10 (base)	DCF	YE2011	YE2012	YE2013	YE2014	YE2015	YE2016	YE2017
0	1	2	3	4	5	6
WACC:	9%	WACC
EVA spread:	1%
397	Revenues	476	557	635	712	783	845	905
12%	yoy growth	20%	17%	14%	12%	10%	8%	7%
EBIT margin %	40%	43%	45%	48%	51%	55%	60%	OM%
EBIT	191	240	286	342	399	465	543
Tax rate	27%	27%	28%	29%	30%	30%	30%
NOPAT	139	175	206	243	279	326	380
adj. EPS	10,70	13,46	15,84	18,66	21,50	25,04	29,23
yoy growth	26%	18%	18%	15%	16%	17%
Dep. & A.	67	78	89	100	110	118	127
1,3	Total Capex	- 87	- 101	- 116	- 130	- 142	- 154	- 165
14,0%	Change in NWC	- 11	- 11	- 11	- 11	- 10	- 9	- 8
FCF	108	140	168	202	237	281	334
Stock Price	385	SENSITIVITY		OM%
EV	4.828	385	40%	50%	60%	70%	80%
Equity	5.002	1,0%	211	250	290	330	369
Net Debt	- 174	"g"	2,0%	229	275	320	365	410
Other liab.	-	3,0%	254	307	359	412	465
Nosh	1300	3,5%	270	327	385	442	500
Current price	551	4,0%	288	352	415	478	542
current EV	7.337
DCF Upside %	-30%	stable "g"
3,50%	"g"
in this example: i wanna make sensitivity analysis on stock price (385) which is a indirect function of variables "g" (3.5%) and OM% (60%). stock price is calculated using ALSO a VAN function (a financial function) It gets really cumbersome if I have to explicit every variable!!! in XLS this is elegantly solved by a =TABLE(,) hope for more clarifications,D

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Jul 13, 2011 5:48 AM in response to davidefromrobella

When you give half description of the problem, you get a half accurate answer !

Numbers was never designed as a substitute to XL. It's not targeting the same users.

++-+-+-+-+-+-+-+-++

Apple Human Interface Guidelines:

Apply the 80 Percent Solution

During the design process, if you discover problems with your product design, you might consider applying the 80 percent solution‚ that is, designing your software to meet the needs of at least 80 percent of your users. This type of design typically favors simpler, more elegant approaches to problems.

If you try to design for the 20 percent of your target audience who are power users, your design may not be usable by the other 80 percent of users. Even though that smaller group of power users is likely to have good ideas for features, the majority of your user base may not think in the same way. Involving a broad range of users in your design process can help you find the 80 percent solution.

+-+-+-+-+-+-+-+-+

It seems clear that you aren't among the 80% targeted by Numbers.

I guess that you will spare time using XL or one of its free clones in libreOffice or openOffice.

I repeat because some users don't understand it that neoOffice is no longer free.

Yvan KOENIG (VALLAURIS, France) mercredi 13 juillet 2011 14:47:56

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Reply