Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |