Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SIMILAR NAMES IN A TABLE
I am having a problem in a table that calculates P&L on each security in the
database. It would not distinguish between different classes of security even though each security has a unique identifier. For instance, it calculates SBER and SBERP as having identical P&L in a month where only SBERP has produced P&L In months where both securities produced P&L it calculates the results accurately. What am I doing wrong? |
#2
|
|||
|
|||
Difficult to say without any formulas shown!
But it sounds a bit as if you're using VLOOKUP with the 4th argument omitted or set to TRUE -- Kind Regards, Niek Otten Microsoft MVP - Excel "Dimitri Kryukov" <Dimitri wrote in message ... I am having a problem in a table that calculates P&L on each security in the database. It would not distinguish between different classes of security even though each security has a unique identifier. For instance, it calculates SBER and SBERP as having identical P&L in a month where only SBERP has produced P&L In months where both securities produced P&L it calculates the results accurately. What am I doing wrong? |
#3
|
|||
|
|||
Trying to make things clear:
Database structu date / name_of_security / P&L Table Structure Row input cell: <31-Jul-2002 / <31-Aug-2002 / ..... / <31-May-2005 Column input cell: Extract of unique names from the column name_of_security that's where the problem starts. All the =MATCH() and =VLOOKUP() fuctions are used at a later stage. "Niek Otten" wrote: Difficult to say without any formulas shown! But it sounds a bit as if you're using VLOOKUP with the 4th argument omitted or set to TRUE -- Kind Regards, Niek Otten Microsoft MVP - Excel "Dimitri Kryukov" <Dimitri wrote in message ... I am having a problem in a table that calculates P&L on each security in the database. It would not distinguish between different classes of security even though each security has a unique identifier. For instance, it calculates SBER and SBERP as having identical P&L in a month where only SBERP has produced P&L In months where both securities produced P&L it calculates the results accurately. What am I doing wrong? |
#4
|
|||
|
|||
This is an example, hope it shows up correctly on the web
this is the Database: Date Name P&L 03-Jan AMENP $15,845.00 04-Feb AMEN $32,485.00 05-Feb AMENP $34,654.00 03-Mar AMEN $18,948.00 this is the Table (=dsum(Database,3, Criteria): $101,932.00 <Feb-05 <Mar-05 <Apr-05 AMEN $15,845.00 $82,984.00 $101,932.00 AMENP $15,845.00 $50,499.00 $50,499.00 this is the net monthly P&L (Month+1 - Month) Jan-05 Feb-05 Mar-05 AMEN $15,845.00 $67,139.00 $18,948.00 AMENP $15,845.00 $34,654.00 $- pls note how AMEN = AMEN + AMENP every month while AMENP is calculated correctly "Dimitri Kryukov" wrote: Trying to make things clear: Database structu date / name_of_security / P&L Table Structure Row input cell: <31-Jul-2002 / <31-Aug-2002 / ..... / <31-May-2005 Column input cell: Extract of unique names from the column name_of_security that's where the problem starts. All the =MATCH() and =VLOOKUP() fuctions are used at a later stage. "Niek Otten" wrote: Difficult to say without any formulas shown! But it sounds a bit as if you're using VLOOKUP with the 4th argument omitted or set to TRUE -- Kind Regards, Niek Otten Microsoft MVP - Excel |
#5
|
|||
|
|||
anybody?
"Dimitri Kryukov" wrote: This is an example, hope it shows up correctly on the web this is the Database: Date Name P&L 03-Jan AMENP $15,845.00 04-Feb AMEN $32,485.00 05-Feb AMENP $34,654.00 03-Mar AMEN $18,948.00 this is the Table (=dsum(Database,3, Criteria): $101,932.00 <Feb-05 <Mar-05 <Apr-05 AMEN $15,845.00 $82,984.00 $101,932.00 AMENP $15,845.00 $50,499.00 $50,499.00 this is the net monthly P&L (Month+1 - Month) Jan-05 Feb-05 Mar-05 AMEN $15,845.00 $67,139.00 $18,948.00 AMENP $15,845.00 $34,654.00 $- pls note how AMEN = AMEN + AMENP every month while AMENP is calculated correctly |
#6
|
|||
|
|||
Change the criteria to: ="=AMEN"
Or use a SUMPRODUCT formula instead, e.g.: =SUMPRODUCT(--($B$2:$B$5=$G3),--($A$2:$A$5<H$2),($C$2:$C$5)) Dimitri Kryukov wrote: anybody? "Dimitri Kryukov" wrote: This is an example, hope it shows up correctly on the web this is the Database: Date Name P&L 03-Jan AMENP $15,845.00 04-Feb AMEN $32,485.00 05-Feb AMENP $34,654.00 03-Mar AMEN $18,948.00 this is the Table (=dsum(Database,3, Criteria): $101,932.00 <Feb-05 <Mar-05 <Apr-05 AMEN $15,845.00 $82,984.00 $101,932.00 AMENP $15,845.00 $50,499.00 $50,499.00 this is the net monthly P&L (Month+1 - Month) Jan-05 Feb-05 Mar-05 AMEN $15,845.00 $67,139.00 $18,948.00 AMENP $15,845.00 $34,654.00 $- pls note how AMEN = AMEN + AMENP every month while AMENP is calculated correctly -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Combine query to count products with similar names | Excel Worksheet Functions | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
Pivot table field names | Excel Worksheet Functions |