Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dimitri Kryukov
 
Posts: n/a
Default 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?
  #3   Report Post  
Dimitri Kryukov
 
Posts: n/a
Default

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   Report Post  
Dimitri Kryukov
 
Posts: n/a
Default

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   Report Post  
Dimitri Kryukov
 
Posts: n/a
Default

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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
Combine query to count products with similar names pomalley Excel Worksheet Functions 8 April 22nd 05 02:15 AM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 11:11 PM
Pivot table field names somewhat confused Excel Worksheet Functions 1 December 14th 04 04:15 AM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"