| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Obtain rate for work center, depending on year
On a worksheet, we have 72 work centers, and charge rates for each
year and each center in a table; going out 12 years. W/C 2008 2009 2010 etc 1 15.00 16.98 18.10 2 23.98 24.25 26.98 3 11.00 13.85 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Obtain rate for work center, depending on year
try this
=SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4)) this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric value at the intersect. It would be better to make the 2 & the 2008 cell references. Mike "Pierre" wrote: On a worksheet, we have 72 work centers, and charge rates for each year and each center in a table; going out 12 years. W/C 2008 2009 2010 etc 1 15.00 16.98 18.10 2 23.98 24.25 26.98 3 11.00 13.85 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Obtain rate for work center, depending on year
Thank you Mike, I'll have a run at it.
Pierre On Apr 25, 12:24*pm, Mike H wrote: try this =SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4)) this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric value at the intersect. It would be better to make the 2 & the 2008 cell references. Mike "Pierre" wrote: On a worksheet, we have 72 work centers, and charge rates for each year and each center in a table; going out 12 years. W/C *2008 * * 2009 * * 2010 etc 1 * * 15.00 * * 16.98 * * 18.10 2 * * 23.98 * * 24.25 * * 26.98 3 * * 11.00 * * 13.85 * * 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Obtain rate for work center, depending on year
I should have mentioned, we enter actual dates: 04/25/2009, and not
just the 4 digit year. Still need to return the vaules of the w/c for that year. Thanks Mike. Pierre On Apr 25, 12:24*pm, Mike H wrote: try this =SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4)) this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric value at the intersect. It would be better to make the 2 & the 2008 cell references. Mike "Pierre" wrote: On a worksheet, we have 72 work centers, and charge rates for each year and each center in a table; going out 12 years. W/C *2008 * * 2009 * * 2010 etc 1 * * 15.00 * * 16.98 * * 18.10 2 * * 23.98 * * 24.25 * * 26.98 3 * * 11.00 * * 13.85 * * 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Obtain rate for work center, depending on year
Pierre,
You could use the HLOOKUP function. 1. With your rate table already created for center number and years. a. Highlight the table Insert Name Define Type WorkCenterRate (or other desired name) Add. Giving you a table reference of: =Sheet1!$A$1:$M$73 2. Create a New Sheet(2). A1=Leave Blank A2 to M2= Use the same headers that you used on Sheet(1). A3 to A74= Number them 1 through 72 B3=the following formula: =if($A$1="","",HLOOKUP(YEAR($A$1),WorkCenterRate,( $A3+1)) The Pull down the formula in the B column The "($A3+1)" references the row number to look at on sheet(1). -- Add MS to your News Reader: news://msnews.microsoft.com Rich/rerat (RRR News) <message rule <<Previous Text Snipped to Save Bandwidth When Appropriate "Pierre" wrote in message ... Thank you Mike, I'll have a run at it. Pierre On Apr 25, 12:24 pm, Mike H wrote: try this =SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4)) this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric value at the intersect. It would be better to make the 2 & the 2008 cell references. Mike "Pierre" wrote: On a worksheet, we have 72 work centers, and charge rates for each year and each center in a table; going out 12 years. W/C 2008 2009 2010 etc 1 15.00 16.98 18.10 2 23.98 24.25 26.98 3 11.00 13.85 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Obtain rate for work center, depending on year
Mike,
Works like a charm. Thank's so much! Pierre Rich, thanks for your input as well On Apr 25, 2:11*pm, "Rich/rerat" wrote: Pierre, You could use the HLOOKUP function. 1. With your rate table already created for center number and years. * * a. Highlight the table Insert Name Define Type WorkCenterRate (or other desired name) Add. Giving you a table reference of: * *=Sheet1!$A$1:$M$73 2. Create a New Sheet(2). A1=Leave Blank A2 to M2= Use the same headers that you used on Sheet(1). A3 to A74= Number them 1 through 72 B3=the following formula: =if($A$1="","",HLOOKUP(YEAR($A$1),WorkCenterRate,( $A3+1)) The Pull down the formula in the B column The "($A3+1)" references the row number to look at on sheet(1). -- Add MS to your News Reader: news://msnews.microsoft.com Rich/rerat (RRR News) * *<message rule <<Previous Text Snipped to Save Bandwidth When Appropriate "Pierre" wrote in message ... Thank you Mike, I'll have a run at it. Pierre On Apr 25, 12:24 pm, Mike H wrote: try this =SUMPRODUCT((A2:A4=2)*(B1:D1=2008),(B2:D4)) this looks for w/c 2 in A2:A4 and 2008 in B1:D1 and returns the numeric value at the intersect. It would be better to make the 2 & the 2008 cell references. Mike "Pierre" wrote: On a worksheet, we have 72 work centers, and charge rates for each year and each center in a table; going out 12 years. W/C 2008 2009 2010 etc 1 15.00 16.98 18.10 2 23.98 24.25 26.98 3 11.00 13.85 15.65 etc. Would like to obtain the rate for each workcenter on another sheet for the year thats entered into cell A1 in the worksheet. A bunch of IF statements. . .=IF(YEAR(D14)=2008,TRUE,FALSE) could be really clumsy as a place to start, as there are 12 years to chose from, making the formulas particularly messy. Thoughts? Pierre- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Obtain a Year to Date result without all cells of data being fille | Excel Worksheet Functions | |||
| unmerge cells when the merge and center button doesn't work | Excel Worksheet Functions | |||
| Multiply by a rate depending on the value | Excel Worksheet Functions | |||
| Display number of day of month depending on the year | Excel Discussion (Misc queries) | |||
| Work out Credit card Payments to obtain an end date | Excel Discussion (Misc queries) | |||
Linear Mode
