MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Two way input table


Posted by Peter on April 03, 2001 7:50 AM

I need to use a two way input table to calculate a 1% increase each year for 5 years. I have been able to do this but unable to add the starting value with the 1% increased value, so the 1% calculation for the second year starts with the first year value + 1%, so on and so forth. Can anyone assist me


Posted by Mark W. on April 03, 2001 8:22 AM

Peter, what's your table function? How have
you layed out your table so far?

Posted by Peter on April 03, 2001 8:30 AM

Table function is to calculate the price in oil for 6 loories. I have put the oil value in 6 columns and have used each row for the 1% increase


Posted by Mark W. on April 03, 2001 8:35 AM

What formula are you using? 'Oil value'*Percent?
What is your starting percentage? What are the
6 oil values?

Posted by Peter on April 03, 2001 8:46 AM

Would it be easier to email sheet?

Posted by Mark W. on April 03, 2001 8:51 AM

Just need 9 data elements...

1. Your table formula
2. Start and end percentages
3. 6 oil values

Posted by Peter on April 03, 2001 8:58 AM

Re: Just need 9 data elements...

Table formula oil*1%
Start % = 1%
End % = 1%
Oil value 1=£0.41329
oil value 2=£0.38275
Oil Value 3=£0.22549
Oil Value 4=£0.20781
Oil Value 5=£0.20937
Oil Value 6=£0.18894


Posted by Mark W. on April 03, 2001 9:03 AM

Question...

I understood from your initial problem description
that you wanted to increase your percentages in
increments of 1%. Why is your start and end
percentages the same?

Posted by Peter on April 03, 2001 9:07 AM

Re: Question...

Well the first year I want to see what 1% increase is and then add that to the starting figure and use the new figure and add another 1% on, until the 5 years is completed.

Posted by Mark W. on April 03, 2001 9:16 AM

Okay, here's my interpetation of your request...

1. Enter the formula, =A1*B1, into cell A3
2. Enter the value 1% into cell A4
3. Enter the formula, =A4+.01, into cell A5 and
copy down to cell A8
4. Enter the values {0.41329,0.38275,0.22549,0.20781,0.20937,0.18894}
into cells B3:G3
5. Select cells A3:G8
6. Choose the Data Table... menu command
7. Enter $A$1 into the "Row input cell" field and
$B$1 into the "Column input cell"
8. Press OK

I have serious doubts about whether this satisfies
your request. If not, let's modify this table
until we do.

Posted by Mark W. on April 03, 2001 9:23 AM

Revision....

Perhaps, the table formula in cell A3 should be:

=A1*B1+A1

Posted by Peter on April 03, 2001 9:43 AM

Re: Revision....

Not quite working Mark

Posted by Mark W. on April 03, 2001 9:50 AM

Re: Debugging...

Did you get these values in cells B4:G8...

{0.4174229,0.3865775,0.2277449,0.2098881,0.2114637,0.1908294
;0.4215558,0.390405,0.2299998,0.2119662,0.2135574,0.1927188
;0.4256887,0.3942325,0.2322547,0.2140443,0.2156511,0.1946082
;0.4298216,0.39806,0.2345096,0.2161224,0.2177448,0.1964976
;0.4339545,0.4018875,0.2367645,0.2182005,0.2198385,0.198387}

If so, what values would you like to see?
Are you using =A1*B1+A1 as your table formula?

Posted by Peter on April 04, 2001 12:28 AM

Re: Debugging...

Sorted now - thanks

Posted by Peter on April 04, 2001 12:30 AM

Re: Debugging...

Thanks Mark sorted.