| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return an address from a lookup into a table?
In my workbook, one worksheet (Tab1) should contain several formulas
structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
Sorry, I didn't quite get it.
What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
Why not simplify matters?
Starting date in C1 of Sheet1, Ending date in C2 of Sheet1, Range on Sheet2: A2 to A100 - dates (in any order) B2 to B100 - values Try this formula: =SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<= C2)*Sheet2!B2:B100)/ SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<=C 2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
checksA would have dates and checksD the values
r2 date1 and s2 date2 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
Thanks, it worked.
"Ragdyer" wrote: Why not simplify matters? Starting date in C1 of Sheet1, Ending date in C2 of Sheet1, Range on Sheet2: A2 to A100 - dates (in any order) B2 to B100 - values Try this formula: =SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<= C2)*Sheet2!B2:B100)/ SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<=C 2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
Right, but there is a problem - the formula, as I wrote it, is not being
accepted (and I did not forget the ctrl-shift-enter). I reviewed it carefully and it seems consistent with yours. Could you please review it, just in case you left out something? Sorry for all the trouble. BTW, your solution looks very elegant to me. "Don Guillett" wrote: checksA would have dates and checksD the values r2 date1 and s2 date2 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
Did you try to retype Don's formula, or did you copy it from the newsgroup
and paste into your formula toolbar? When you say "it is not being accepted", what error message did you get from Excel? Normally if Excel won't accept a formula it will try to put the cursor where it thinks there may be an error. If so, where does it put the cursor? Don has assumed that your Windows regional settings use a comma as a list separator; do your settings use a semi-colon? If so, replace the comma in Don's formula by a semi-colon. Are you sure that you've got the required named ranges ChecksA and ChecksD? [You can check with Insert/ Name/ Define...] If not, Excel will still accept the formula, but you will get a #NAME? result from the formula. Are you sure that the two named ranges have the same length? -- David Biddulph Chap wrote: Right, but there is a problem - the formula, as I wrote it, is not being accepted (and I did not forget the ctrl-shift-enter). I reviewed it carefully and it seems consistent with yours. Could you please review it, just in case you left out something? Sorry for all the trouble. BTW, your solution looks very elegant to me. "Don Guillett" wrote: checksA would have dates and checksD the values r2 date1 and s2 date2 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to return an address from a lookup into a table?
It was the list separator, how stupid of me.
The formula works perfectly, and looks brilliant. Many thanks, guys! "David Biddulph" wrote: Did you try to retype Don's formula, or did you copy it from the newsgroup and paste into your formula toolbar? When you say "it is not being accepted", what error message did you get from Excel? Normally if Excel won't accept a formula it will try to put the cursor where it thinks there may be an error. If so, where does it put the cursor? Don has assumed that your Windows regional settings use a comma as a list separator; do your settings use a semi-colon? If so, replace the comma in Don's formula by a semi-colon. Are you sure that you've got the required named ranges ChecksA and ChecksD? [You can check with Insert/ Name/ Define...] If not, Excel will still accept the formula, but you will get a #NAME? result from the formula. Are you sure that the two named ranges have the same length? -- David Biddulph Chap wrote: Right, but there is a problem - the formula, as I wrote it, is not being accepted (and I did not forget the ctrl-shift-enter). I reviewed it carefully and it seems consistent with yours. Could you please review it, just in case you left out something? Sorry for all the trouble. BTW, your solution looks very elegant to me. "Don Guillett" wrote: checksA would have dates and checksD the values r2 date1 and s2 date2 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... Sorry, I didn't quite get it. What would be the named ranges? ChecksA and ChecksD? For the dates and for the target values? And the Date1 and Date2 values I mentioned, where would they come in? As R2 and S2? "Don Guillett" wrote: Assumes named ranges and valid dates. An array formula that must be entered using ctrl + shift +enter =AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chap" wrote in message ... In my workbook, one worksheet (Tab1) should contain several formulas structured as: =AVERAGE('Tab2'!Address1:Address2) The other worksheet (Tab2) contains a table, in which column A are all dates from the last 5 years, in ascending order, and column B the corresponding target values. Address1 and Address2 are the cells in column B of Tab2 located on the same rows as the cells in column A that contain Date1 and Date2, respectively. Date1 and Date2 are cells (containing dates) in Tab1. My goal is to have each formula in Tab1, given Date1 and Date2, calculate an average of target values retrieved from the table in Tab2. How should I complete the formulas? Should a Lookup function be used? Many thanks. Chap |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP & RETURN CELL ADDRESS | Excel Worksheet Functions | |||
| Lookup, and Return Cell Address | Excel Worksheet Functions | |||
| VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions | |||
| V Lookup and return cell address | Excel Worksheet Functions | |||
| need Lookup table to return null or zero | Excel Worksheet Functions | |||
Linear Mode
