List all possible results of a formula

[Fraxav]

Hi,

I am not sure if this falls into the formula or VBA realm, but I am trying to plot all the possible values in a reference cell against the corresponding result of a dependent formula.
In the example below, the value in cell F4 is used by the formulas in G6:G24 to return a several "cost per sample" values, which are then added up to the total in G25. I am trying to plot the number of samples (F4) against the resulting total cost (G25) for each sample number between 1-100. To do so I would need a table of course, but how to make said table automatically without me having to manually type every combination of F4 and G25?

 

[Fluff]

Is this what you mean

+Fluff 1.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
5			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
6	0.187	1	0.187	0.374	0.561	0.748	0.935	1.122	1.309	1.496	1.683	1.87	2.057	2.244	2.431	2.618	2.805	2.992	3.179	3.366	3.553	3.74
7	0.12	1	0.12	0.24	0.36	0.48	0.6	0.72	0.84	0.96	1.08	1.2	1.32	1.44	1.56	1.68	1.8	1.92	2.04	2.16	2.28	2.4
8	0.01	1	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09	0.1	0.11	0.12	0.13	0.14	0.15	0.16	0.17	0.18	0.19	0.2
9	0.021	0.333333	0.007	0.014	0.021	0.028	0.035	0.042	0.049	0.056	0.063	0.07	0.077	0.084	0.091	0.098	0.105	0.112	0.119	0.126	0.133	0.14
10	4.378	1	4.378	8.756	13.134	17.512	21.89	26.268	30.646	35.024	39.402	43.78	48.158	52.536	56.914	61.292	65.67	70.048	74.426	78.804	83.182	87.56
11	1.51	1	1.51	3.02	4.53	6.04	7.55	9.06	10.57	12.08	13.59	15.1	16.61	18.12	19.63	21.14	22.65	24.16	25.67	27.18	28.69	30.2
12	0.01	1	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09	0.1	0.11	0.12	0.13	0.14	0.15	0.16	0.17	0.18	0.19	0.2
13	0.001	33.95	0.03395	0.0679	0.10185	0.1358	0.16975	0.2037	0.23765	0.2716	0.30555	0.3395	0.37345	0.4074	0.44135	0.4753	0.50925	0.5432	0.57715	0.6111	0.64505	0.679
14	0.191	17.5	3.3425	6.685	10.0275	13.37	16.7125	20.055	23.3975	26.74	30.0825	33.425	36.7675	40.11	43.4525	46.795	50.1375	53.48	56.8225	60.165	63.5075	66.85
15	1.75	0.035	0.06125	0.1225	0.18375	0.245	0.30625	0.3675	0.42875	0.49	0.55125	0.6125	0.67375	0.735	0.79625	0.8575	0.91875	0.98	1.04125	1.1025	1.16375	1.225
16	1.75	0.035	0.06125	0.1225	0.18375	0.245	0.30625	0.3675	0.42875	0.49	0.55125	0.6125	0.67375	0.735	0.79625	0.8575	0.91875	0.98	1.04125	1.1025	1.16375	1.225
Main

Cell Formulas
Range	Formula
G5:DB5	G5	=SEQUENCE(,100)
G6:DB16	G6	=E6:E16*F6:F16*SEQUENCE(,100)
Dynamic array formulas.

 

[Eric W]

You might also look into Data Tables, which is a built-in tool that does what you're asking. Check out this link:

Data Tables in Excel

Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table in Excel.

www.excel-easy.com

 

[Fraxav]

Thank you very much both of you! Fluff, I think what you did is actually what I was looking for, but I only needed to propagate the formula for the total. Good to know that this can be achieved with formulas too! However, data tables are a much simpler way (for me, at least) to accomplish what I wanted so I have marked that as the solution for other newbies like me

 

[Fluff]

Glad we could help & thanks for the feedback.