"Goal Seek" expanded to Table

[Salem 80]

Dear Sir,
I am currently using the "goal seek" function under the Data - What-if analysis tap, to Back calculate a value in a table. My question is whether it is possible to extend this function to an entire table instead of using the same function on each cell in that table repeatedly?

Originally I have in table "A" several input values, table B displays the output or results calculated from the input data in table "A" , both table "A" and "B" have the same number of rows and columns, i.e. each one cell in table "B" corresponds to only one unique cell in table "B", but the formulas used in table "B" cells are not 100% identical, several imbedded constants differ between each cell.
The equations used in each cell in table "B" are rather complicated and time consuming to create.
A new case has appeared where the end results previously calculated in table "B" are now given as an input data, and the values previously given in table "A" are requested as a required end value.

Instead of reversing the equations given in table "B" to calculate values in "A" , I used the "goal seek" function under the Data - What-if analysis tap as mentioned above, but applying this to each cell is time consuming.

Is there a more efficient method to use for this purpose?

Thank you in advance.

 

[BadgerRoad]

It would be helpful if you provided sample data showing your equations and what you are trying to do.

 

[Salem 80]

Thank you for your reply.
Attached is a reduced sheet as requested. In addition to the two tables A & B mentioned in the first post, the sheet includes two sets of fixed constants, these are included to provide a real case, as they are included in the calculations in table B.
The goal is to enter a value in Table B and obtain a result in table A, i.e. the opposite of the current situation, basically solving the equation for "A" instead of "B".

I have actually performed this task yesterday mathematically for one of he cells, but still would like to know if I can use the "goal seek" function under the Data - What-if analysis tap in a more efficient way, or even better learn if there are other ways for looking at the whole situation from a different angle.

Best Regards

It seems I am having a problem attaching the excel file, I will try to send it via mail

 

[BadgerRoad]

A better option is to use XL2BB. You can find information on how to install it on the board.

 

[Salem 80]

Thanks for the guidance.
On the first trail I got a message saying that the generated content exceeds the limit that can be posted on the forum , so as advised selected a smaller range, but that is ok, since the excluded ranges are basically a semi repetition of the copied range.

Mr Excel - 1.xlsx
	B	C	D	E	F	G	H	I	J
2	Table A, Originally the Initial starting data						Table B , Originally "The results"
3	550.00	510.00	555.00				835.11	793.88	844.07
4	774.00	658.00	415.00				1039.72	923.55	682.12
5	998.00	806.00	275.00				1305.90	1111.77	549.45
6
7	Constants, Set No.1						Constants, Set No.2
8	28.000	28.000	29.000				1.20	0.30	0.45
9	6.000	8.000	9.000				5.60	5.60	5.60
10	45.000	46.000	18.000				91.00	91.00	91.00
11							4.50	4.50	4.50
12							16.41	16.41	16.41
13							13.00	13.20	11.90
14							15.25	15.23	17.00
15							0.50	0.50	0.50
16							101.00	101.00	104.00
Tables

Cell Formulas
Range	Formula
H3:J5	H3	=(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+(Tables!H$11)+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+(Tables!B8)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+Tables!H$11+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+Tables!B8)*0.0015))+(Tables!H$12)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+(Tables!H$11)+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+(Tables!B8)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+Tables!H$11+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+Tables!B8)*0.0015))+Tables!H$12)*0.003+Tables!H$13)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+(Tables!H$11)+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+(Tables!B8)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+Tables!H$11+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+Tables!B8)*0.0015))+(Tables!H$12)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+(Tables!H$11)+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+(Tables!B8)+(((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10)+Tables!H$11+((Tables!B3+Tables!H$8+Tables!H$9+Tables!H$10+Tables!H$11)*0.003)+Tables!B8)*0.0015))+Tables!H$12)*0.003+Tables!H$13)*0.005+Tables!H$14+Tables!H$15+Tables!H$16

 

[BadgerRoad]

Can you be more specific? Which value in Table B do you want to enter and then which value in Table A do you want to solve for?

 

[Salem 80]

Good evening,
For the tables shown in my previous reply that represent the current existing situation, for example, if we enter a value of 550 in cell B3 in table "A" we will get the value of 835.11 in cell H3 in table "B" this value is generated from the formula in cell H3. The formula used in the cells in table "B" also use constants from the tables Set No.1 and Set No.2 , these are called constants because in reality they are not changing, in contrast to the values in table "A" that do change.

The same goes for each cell in table "A", another example, the value of 510 in cell C3 in table "A" will give a value of 793.88 in cell I3 in table "B".

hope this helps explain in a more clear way.

 

[BadgerRoad]

Ok, so you want to specify a value in H3 and have Goal Seek calculate the value in B3, etc. What is the problem?

 

[Salem 80]

Correct, but table "B" and "A" in realty are much larger than three columns by three rows, and I am looking for a faster way to calculate all the values for the cells in the large table "B" as an alternative to calculating each cell individually using Goal Seek each time cell by cell.