This is a mobile optimized page that loads fast, if you want to load the real page, click this text.

Microsoft Excel Q&A

Jump to new
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).
 
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)
 

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.
 
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.
 
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
 
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.
 
Trembling Hand said:
Better to see if the data provider has day High/Low field?
Click to expand...

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.


Nah. Circular reference as you can't compare cell A2 with itself...
 
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.
 
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
 
Pager said:
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
Click to expand...

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!!
 
skc said:
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!!
Click to expand...

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

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

Cheers

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
 
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).
 
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
 
Cool

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

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
 
You must log in or register to reply here.

Similar threads

R
Best Books I Read Last Year
Replies
18
Views
4K
MDWake
J
Advice for a newbie
Replies
10
Views
4K
divs4ever
R
The 7 Moats of Quality Companies
Replies
7
Views
7K
divs4ever
Cheap or free Microsoft Office
Replies
0
Views
3K
sydboy007
The Beginner Trader's Manual Guide
Replies
9
Views
20K
commoyo
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more...