Two-Input table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Allowence $au 2500
Exchange rate 75%
Converted rate 1875

How do i do a two input table to calculate rates on a table
A B C D E
$1875 1000 1500 2000 2500
50%
55%
60%
70%
so it calcualtes the rates for me? i have fiddles around with a few
different ways but am getting no where fast.
Please Help and thanks in advanced...I am a new user so be sensitive
please....
 
In A1: 2500, in B1: 75%, then in C1: =A1*B1
C1 is the base formula, A1 & B1 are the 2 variables

To set-up the 2 variable data table
Place in say, B3: =C1
(just a simple link pointing to the base formula cell)

List sample values for row inputs in C3:H3, eg: 1000, 1500, .. 3500
List sample values for col inputs in B4:B12, eg: 50%, 55%, .. 90%

Select B3:H12
Click Data > Table
In the Table dialog, enter for
Row input: A1
Column input: B1
Click OK. That's it!. The data table's done ..
 
Thanku kindly Max... I will give it ago tonight when children go to
bed...Have a look in tomorrow just incase i need your help...Once
again...many thanks

Justina
 
Hi Justina

In addition to what Max has given you, do ensure that in
Tools>options>Calculation you are set to Automatic, and not Automatic
except Tables
Otherwise you will have a frustrating time wondering why Max's file
works, and yours doesn't.
 
Ok iam still having difficulty working this one out. I have done all i am
suppose to but when i click ok after completing all tasks it give me the 1875
figure in every cell on my table...Eeeek, what am i doing wrong...this is
what was happening to be before i posted thread. I just cant seem to figure
it out...Please help...
 
Hi Max, yes i have copied the example axactly, the only problem i can see
with mine is cell B3 is reconising it as 1875 not =C1, i have added a coment
to both C1 & B3 but how do i link them? excuse my inexperience please
 
Hi Justina

I have been caught out before, with exactly what you describe. That's
why I said to check Tools>Options>Calculation and ensure it is set to
Automatic.
 
Perhaps try set it up again in a brand new sheet.
Click Insert > Worksheet to insert a new sheet

Then click Tools > Options > Calculation tab
and ensure that Automatic is checked > OK
(as Roger mentioned)

Ensure you have these 2 formulas
In C1: =A1*B1
In B3: =C1

The cell comments I placed for C1 & B3 in the sample was just for clarity.
 
i have chkd on Automatic, not auto except tables option...how do i get cell
B3 to have the formula =C1??? This is where it is tripping me up when i click
on this cell it reads 1875 as per table but i know it needs the formula as
mentioed how to i insert the formula to this cell??? please just give me one
more try...
 
Yay!!! I got it....I was right i needed that formula in B3, worek it out with
your help guys, thankyou so very, very much...
PS, Max i have just signed up also.....
 
Back
Top