Another Excel Question

[mik1973]

Hoping someone help (again)

Ok - I have two columns- the first is titled currency trade where i have listed below cable swissy- euro (there will be multiple instances of these in this particular column)- in the next column (amount ) i have various values . What i'd like to do is enter a formula where i can ask exel to find the number of instances it recognises the euro symbol in column 1 then add its total values from column 2. Ive tried under data sort but have had no luck so far - hope someone can help

Cheers,

Mick

 

[blackcab]

If labels are in A1:A100 and values are in B1:B100 and you want to sum values for the label "EUR/USD" then:

=SUMIF(A1:A100,"EUR/USD",B1:B100)

 

[mik1973]

Hi Thanks for your reply.

I thought there was another way around this by going into data sort but i just can't remember how i got round this the last time.

Cheers,

Mick

 

[the blades]

Hi Thanks for your reply.

I thought there was another way around this by going into data sort but i just can't remember how i got round this the last time.

Cheers,

Mick

Hi Mick,

Have you tried the databse functions (DSUM, DAVERAGE etc)

Create a list with your criteria listed in the left most columns, and your data to the right, as many columns as you like.

Create a seperate field with the criteria you want to apply the function to.

Link these fields to the function cell (sounds hardeer than it is, honestly!)

Sorry that explanation reads a bit crappy - try the function wizard, then use help for database fuctions. If you don't get on with it, PM me and I'll send you a table I use to track my performance with in this way;

eg - average of all trades meeting the following criteria - 2005, shorts, trade system 2 etc...

Cheers,
UTB

 

[mik1973]

HI UTB,
Thanks for your response it is appreciated. I remember doing something similar last year but i'm sure i highlighted the 2 columns of cells then went through a wizard type of operation- via data sort or filter. I've been racking my brains on how to do it but still havent found what i'm looking for (queue the music..)

Thanks again,

Mick

 

[Aleph Sigma Chi]

mik1973 - if you could paste up an example of your data it might make it easier to visualize.

 

[mik1973]

Hi Aleph,
Example of what i'm looking to do (i'll keep the figures pretty basic-no reflection on actual trading performance):

Currency Profit
USD 55
Euro

 

[mik1973]

Sorry bout that hit the wrong key

Currency Profit
USD 55
Euro 27
USD 36
Euro 42
USD 17


What i'd like to do is to calculate the profit for e.g USD (however my list would be much longer- kept this short for illustration purposes)- i'm sure there was a wizard facility in a previous version of exel but the closest i can see in the most recent version would be a pivot table.

Hope this is makes this a bit more clear of what i'm trying to do.

Thanks in advance for any help

Mick

 

[the blades]

Sorry bout that hit the wrong key

Currency Profit
USD 55
Euro 27
USD 36
Euro 42
USD 17


What i'd like to do is to calculate the profit for e.g USD (however my list would be much longer- kept this short for illustration purposes)- i'm sure there was a wizard facility in a previous version of exel but the closest i can see in the most recent version would be a pivot table.

Hope this is makes this a bit more clear of what i'm trying to do.

Thanks in advance for any help

Mick

Mick,

Look at the table attached.

Hope this helps but let me know if you're after something else?

Cheers,
UTB

 

[mik1973]

Blades!!
What a star!

Thanks very much- is it ok if i copy and paste your spreadsheet?

Thanks Again,

Mick

 

[the blades]

Blades!!
What a star!

Thanks very much- is it ok if i copy and paste your spreadsheet?

Thanks Again,

Mick

no bother - that's why I attached it. There are many other useful database functions to add (see help). Also note that you can have other columns is your results to apply the formula's to (eg average result, average trade size etc). The "4" within the formula refers to the column number (from the left) in the table. if you add more columns, change this number accordingly, and widen the cell references in the first bit.

Also forgot to add the key for the instrumenst (in this vase USD/GBP etc).

Cheers,
UTB