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?

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?

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?

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

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

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