Need a Formula
 

I need a formula to calculate the amount of liquidated damages. I have what
is called the cost share amt. in one column, another column has the contract
year, another column has the conservation practice number. I need a formula
for a 4th column that will put a percentage, which it pulls from a chart in
the same workbook. The chart is set up by year and practice code to figure
the percentage of cost share that will be paid. And then I need a formula
that will figure out what the percentage amount is in dollars, which is the
liquidated damages.

Need a Formula
 

Percentage

=INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$ 1000=B2)*(Sheet2!$B$1:$B$1
000=C2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Amount

=D2*A2

where A2 is the cost share amt, B2 is the year, C2 is the practice number,
and D2 is the result of the first formula. The chart (table) is on Sheet2 in
A1:C1000)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Susan" wrote in message
...
I need a formula to calculate the amount of liquidated damages. I have
what
is called the cost share amt. in one column, another column has the
contract
year, another column has the conservation practice number. I need a
formula
for a 4th column that will put a percentage, which it pulls from a chart
in
the same workbook. The chart is set up by year and practice code to
figure
the percentage of cost share that will be paid. And then I need a formula
that will figure out what the percentage amount is in dollars, which is
the
liquidated damages.

Need a Formula
 

Bob,

I used the Lookup Wizard to create a similar formula which is
=INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.

I am creating a form where the employee enters the contract year, the
practice code number and hopefully the percentage from the penalty table is
automatically filled in. There will be several lines where they will be
filling in this information for contract holders.

I also don't understand what you mean by Ctrl-Shift-Enter to commit the
formula.

Susan

"Bob Phillips" wrote:

Percentage

=INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$ 1000=B2)*(Sheet2!$B$1:$B$1
000=C2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Amount

=D2*A2

where A2 is the cost share amt, B2 is the year, C2 is the practice number,
and D2 is the result of the first formula. The chart (table) is on Sheet2 in
A1:C1000)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Susan" wrote in message
...
I need a formula to calculate the amount of liquidated damages. I have
what
is called the cost share amt. in one column, another column has the
contract
year, another column has the conservation practice number. I need a
formula
for a 4th column that will put a percentage, which it pulls from a chart
in
the same workbook. The chart is set up by year and practice code to
figure
the percentage of cost share that will be paid. And then I need a formula
that will figure out what the percentage amount is in dollars, which is
the
liquidated damages.

Need a Formula
 

I don't understand what you are telling me here, it bears little relation to
what I posted.

By Ctrl-Shift-Enter to commit the formula, I mean holding down those three
keys together after entering the formula, rather than just Enter


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Susan" wrote in message
...
Bob,

I used the Lookup Wizard to create a similar formula which is
=INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.

I am creating a form where the employee enters the contract year, the
practice code number and hopefully the percentage from the penalty table
is
automatically filled in. There will be several lines where they will be
filling in this information for contract holders.

I also don't understand what you mean by Ctrl-Shift-Enter to commit the
formula.

Susan

"Bob Phillips" wrote:

Percentage


=INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$ 1000=B2)*(Sheet2!$B$1:$B$1
000=C2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

Amount

=D2*A2

where A2 is the cost share amt, B2 is the year, C2 is the practice
number,
and D2 is the result of the first formula. The chart (table) is on
Sheet2 in
A1:C1000)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Susan" wrote in message
...
I need a formula to calculate the amount of liquidated damages. I
have
what
is called the cost share amt. in one column, another column has the
contract
year, another column has the conservation practice number. I need a
formula
for a 4th column that will put a percentage, which it pulls from a
chart
in
the same workbook. The chart is set up by year and practice code to
figure
the percentage of cost share that will be paid. And then I need a
formula
that will figure out what the percentage amount is in dollars, which
is
the
liquidated damages.