Microsoft Excel Q&A

[Pager]

Hi, New to using Excel

Can anyone help me with the following?

I have 5 sets of data in columns, with Date and a dollar value and want to merge all 5 data values by date, e.g.

Data 1 in columns C (date) and D ($ amount) has

16/3/2010 and a value of $156
22/3/2010 and a value of $213
24/3/2010 and a value of $450

Data 2 in columns E (date) and F ($ amount) has

20/3/2010 and a value of $732
21/3/2010 and a value of $170
24/3/2010 and a value of $167

Data 3 in columns H (date) and I ($ amount) has

18/3/2010 and a value of $332
20/3/2010 and a value of $180
26/3/2010 and a value of $165

Data 4 and 5 are similar values with some same dates and some different.

I want to show each date with a combined dollar value and I have about 5 years to combine, as there is no date if there is no value then using auto sum on each row wont reflect the data I want to combine for that day, anyone know how I do this

 

[Bushman]

Hi, New to using Excel

Can anyone help me with the following?

I have 5 sets of data in columns, with Date and a dollar value and want to merge all 5 data values by date, e.g.

Data 1 in columns C (date) and D ($ amount) has

16/3/2010 and a value of $156
22/3/2010 and a value of $213
24/3/2010 and a value of $450

Data 2 in columns E (date) and F ($ amount) has

20/3/2010 and a value of $732
21/3/2010 and a value of $170
24/3/2010 and a value of $167

Data 3 in columns H (date) and I ($ amount) has

18/3/2010 and a value of $332
20/3/2010 and a value of $180
26/3/2010 and a value of $165

Data 4 and 5 are similar values with some same dates and some different.

I want to show each date with a combined dollar value and I have about 5 years to combine, as there is no date if there is no value then using auto sum on each row wont reflect the data I want to combine for that day, anyone know how I do this

The concatenate function allows you to join two cells i.e. =concatenate(A1,A2).

 

[Pager]

Thanks but doesn’t seem to work, all I get is 5 values in one cell, e.g. $450, $167 etc etc, what I want is the combined total of those 5 cells but corresponding to the dates.

So using my example above, on March 24th the total is $617, I want the formula to ignore the data 3 entry(26th March) which is $165 as although its in the same row as data 1 and 2, its not the same date.

Can this be done in Excel

 

[skyQuake]

Thanks but doesn’t seem to work, all I get is 5 values in one cell, e.g. $450, $167 etc etc, what I want is the combined total of those 5 cells but corresponding to the dates.

So using my example above, on March 24th the total is $617, I want the formula to ignore the data 3 entry(26th March) which is $165 as although its in the same row as data 1 and 2, its not the same date.

Can this be done in Excel

Suppose column A has all the dates in order. You would do:

=vlookup(A1,C1: D3,2,FALSE)+vlookup(A1,E1:F3,2,FALSE)+vlookup(A1,H1:G3,2,FALSE)+ etc...

what that does it:
=vertical lookup(the date required, in the range C1 to D3, if its found return the value in the 2nd column ie the $value, FALSE just makes sure it looks for exact value rather than approx value)

 

[skc]

Hi, New to using Excel

Can anyone help me with the following?

I have 5 sets of data in columns, with Date and a dollar value and want to merge all 5 data values by date, e.g.

Data 1 in columns C (date) and D ($ amount) has

16/3/2010 and a value of $156
22/3/2010 and a value of $213
24/3/2010 and a value of $450

Data 2 in columns E (date) and F ($ amount) has

20/3/2010 and a value of $732
21/3/2010 and a value of $170
24/3/2010 and a value of $167

Data 3 in columns H (date) and I ($ amount) has

18/3/2010 and a value of $332
20/3/2010 and a value of $180
26/3/2010 and a value of $165

Data 4 and 5 are similar values with some same dates and some different.

I want to show each date with a combined dollar value and I have about 5 years to combine, as there is no date if there is no value then using auto sum on each row wont reflect the data I want to combine for that day, anyone know how I do this

Why is the data in 6 different columns? Can they be arranged in just two columns (date and $ value)? Or if they are actually 3 different sets of data (e.g. from different accounts), then you can add a data column called "Accout" or something to record that.

If you have all the data in 2 (or 3) columns, you can use the pivot table function which will put each distinctive dates in rows and sum the $ value against them.

PM me part of the spreadsheet if you are stuck.

 

[Synergy]

I think the SUMIF function is the one you're after. VLOOKUP will work but will be a little clunky in dealing with dates that don't exist.

=SUMIF($C$2:$C$4,"="&A2,$D$2:$D$4)+SUMIF($E$2:$E$4,"="&A2,$F$2:$F$4)

With a list of all dates in column A, and this formula filled down in column B - starting in B2, you should get something close to what you're after. The formula would be much shorter if all your data was in 2 columns though.

 

[skc]

Excel Gurus...

I have API feeding some data into Excel like last price for a stock into say cell A1. This is dynamic and changes throughout the day. I want Excel to be able to record in two other cells (say B1 and C1) the Maximum and Minimum of the dynamic data in A1.

Is there any way of doing that??

Thanks

 

[Trembling Hand]

Better to see if the data provider has day High/Low field?

 

[RazzaDazzla]

Not sure if the formulas will get re-calculated everytime the API updates the spreadsheet.

If they do, then try this.

If the last price is in cell A1, and you want to record Max in A2 and Min in A3;

A2 = if(A1>A2,A1,A2)

A3 = if(A1<A3,A1,A3)

AS I said thought, this will depend on whether or not excel re-calculates every formula every time the API updates the data in A1.

Failing that, there may be some sort of Max() or Min() function that locks in the max or min price.

 

[skc]

Better to see if the data provider has day High/Low field?

As I am pairs trading the max/min I want to record is the trade's P/L. So it's calculated with 2 last prices. The day's high/low between the long and the short leg don't always coincide as you know.

Not sure if the formulas will get re-calculated everytime the API updates the spreadsheet.

If they do, then try this.

If the last price is in cell A1, and you want to record Max in A2 and Min in A3;

A2 = if(A1>A2,A1,A2)

A3 = if(A1<A3,A1,A3)

AS I said thought, this will depend on whether or not excel re-calculates every formula every time the API updates the data in A1.

Failing that, there may be some sort of Max() or Min() function that locks in the max or min price.

Nah. Circular reference as you can't compare cell A2 with itself...

 

[Pager]

Theirs probably a very easy answer to this but as im new to using Excel and generally not to hot with programming could someone please help with the flowing.

I want to be shown in text if the sequence of numerical values in a 3 cells are in a specific order, for example if the value of C3 is below C2 and the value of C1 is below C2 but C3 is above C1 I want the text to say “True” in Cell D3, if its not in that order “False”

Cheers

Pager

 

[RazzaDazzla]

Theirs probably a very easy answer to this but as im new to using Excel and generally not to hot with programming could someone please help with the flowing.

I want to be shown in text if the sequence of numerical values in a 3 cells are in a specific order, for example if the value of C3 is below C2 and the value of C1 is below C2 but C3 is above C1 I want the text to say “True” in Cell D3, if its not in that order “False”

Cheers

Pager

Can't really make sens of your logic;

Something along the lines of;
=IF(C2<C1,TRUE,FALSE) in D3 should start you off.

If you need to compare one cell against two other cells, eg if you have a list of numbers;
10
9
8
7
6
5

and you want to check they are in descending order, then use;
=AND(IF(A2<A1,1,0),IF(A2>A3,1,0))

skc I'll get back to you soon.

 

[Pager]

Maybe this will make it clearer, im entering this formula =if(C1<C2,C3<C2,C3>C1,"True") but its telling me im asking for too many arguments for this function.

How do i edit it to be able to calculate if this is True or False ?

Cheers

Pager

 

[skc]

Maybe this will make it clearer, im entering this formula =if(C1<C2,C3<C2,C3>C1,"True") but its telling me im asking for too many arguments for this function.

How do i edit it to be able to calculate if this is True or False ?

Cheers

Pager

You need to use *AND between the 3 arguments.

=if((C1<C2)*AND(C3<C2)*AND(C3>C1),"True","Flase")

Now back to my question!!

 

[Pager]

You need to use *AND between the 3 arguments.

=if((C1<C2)*AND(C3<C2)*AND(C3>C1),"True","Flase")

Now back to my question!!

Ahhhhhhhhhhh, now i was just trying to use "and" .

Guessed it would be something simple like using the "*"

Cheers

Pager

 

[Pager]

Hope this question makes sense

I have 8 sheets and I want to show the same data in the same cell of each sheet as I add or change the data in just 1 of the sheets, I can do this cell by cell by using = and then going from sheet to sheet but this is very time consuming so how do I do this for a large amount of data ?

Thanks

Pager

 

[Timmy]

I have done this, but it was a while ago…

Let me check I understand you properly:
OK, you have 8 sheets.
If you are entering the data on, say, Sheet 1, and you want the data you enter on Sheet1 to appear on sheets 2, 3, 4, 5, 6, 7, and 8.
Right?

I think you do something like this:
So, assume you enter data into cell A1 on the first sheet, and this data is to appear in cell A1 on each of sheets 2, 3, 4, 5, 6, 7, and 8.

On sheet 2, in cell A1, enter:
=Sheet1!A1

On sheet 3, in cell A1, enter:
=Sheet1!A1

On sheet 4, in cell A1, enter:
=Sheet1!A1

… and so on.

I just tested this out and it works (for me anyway … let me know how it goes).

 

[Pager]

Thanks Timmy

Also found by doing the = thing to the first cell and then enter then dragging down it filled all the cells, which quickens things up as well

 

[Timmy]

Cool

ps. Moving this thread to the
>Trading/Investor Resources
>Software and Data
sub-forum.

 

[Santob]

Hope this question makes sense

I have 8 sheets and I want to show the same data in the same cell of each sheet as I add or change the data in just 1 of the sheets, I can do this cell by cell by using = and then going from sheet to sheet but this is very time consuming so how do I do this for a large amount of data ?

Thanks

Pager

If you're entering the same value/formula into the same cell in each sheet there's a very simple way.

1) Select all the sheets you want to enter that value/forumla into by holding CNTRL and clicking on the sheet names at the bottom of Excel

2) Into *any* of the selected sheets, go to the cell that you want to enter the value/formula into

3) When you finish typing in the value/formula, then press CNTRL+ENTER, rather than just ENTER and that value/formula will get entered into all the selected sheets.

That should save you a LOT of time