2 question for Excel champs ;)

1. I've got two columns, one is called - Auction bill's date,the second is - price.
the problem is that I've got two cells by the same date,so I need to calculate the average of this
two cells, because that this auction bill is listed for the last 10 years,which means its a large amount
of data and I cannot do it manually,so i've been told to use Pivot Table,and im tryin over and over
to use this,and I cant succeed,so can anyone help or have suggestion ?

2. I've got 3 (even 4 but lets stick for the moment on 3) columns,called : Date, Stock,S&P500.
I need to make a graph and compare between the stock and the S&P500 of course according to the
date. well the basic problem is that the S&P500 price is around 1200,and the stock price is around
40, so how can I compare between them,what graph should I use ? I want that from the left of the
graph will shown the stock's price,from the right the S&P's price,and on the buttom will be the date.




any,and i mean - any suggestion will be helpfull,
thank u in advance,
hummiz

Hi,

1. I've got two columns, one is called - Auction bill's date,the second is - price.
the problem is that I've got two cells by the same date,so I need to calculate the average of this
two cells, because that this auction bill is listed for the last 10 years,which means its a large amount
of data and I cannot do it manually,so i've been told to use Pivot Table,and im tryin over and over
to use this,and I cant succeed,so can anyone help or have suggestion ?

Select your data, go to Data > Pivot Table.
Follow instructions. When you see Layout button, click on it, and drag the price column into it. It will show 'SUM of ..''. Click on it and change to AVERAGE. Continue.

2. I've got 3 (even 4 but lets stick for the moment on 3) columns,called : Date, Stock,S&P500.
I need to make a graph and compare between the stock and the S&P500 of course according to the
date. well the basic problem is that the S&P500 price is around 1200,and the stock price is around
40, so how can I compare between them,what graph should I use ? I want that from the left of the
graph will shown the stock's price,from the right the S&P's price,and on the buttom will be the date.

Select your data .. al 3 columns, and plot the XY graph. Select one of the series, and right-click to select format data series. Select the Axis tab. Select the option 'Secondary axis'

Mangesh

Originally Posted by mangesh_yadav

Hi,


Select your data .. al 3 columns, and plot the XY graph. Select one of the series, and right-click to select format data series. Select the Axis tab. Select the option 'Secondary axis'

Mangesh

first of all,great great job!!! and thank u for the faster reply ever,I couldnt try this out at the moment though.

Now,there're two problems :

First I did exactly what you told me,but it does not show the date column,it write other parameters which I don't really know where did it get from,so any idea ?

second, I found out that there's a previous problem which maybe cause the former problem but im not sure : you see,I have to import the Auction bill,S&P500 & the stock's prices to one sheet.
So 3 columns,but their date's length are not the same (I mean,soe of them has traded that day,and some arent),its pretty big data to do it manually,so how can i arrange this 3 columns according to the date which all 3 of them will be shown only if the all 3 of them traded the same day ,and not only 1 or 2 of them ?

thank u this |--------------------------------------------------| much and more
Hummiz

ok i think i made this sounds more complicated then it does,so lets make things straight :

3 files.
each file contain two columns : the 1st is the DATE,the other is something else (1st file - Stock's price,2nd file - S&P500's price,3rd file - Auction bill)
notice - on each file there is the date column! but....

the date on each file is not the same as the others,some of them are the same and some of them aren't.

so I need to make this new sheet,which contain 4 columns : Date,Stock's price,S&P500's price & Auction bill's price, but only according to the date,which means that only if there are the same dates on all 3 of them (stock,S&P500,Auction bill) then this date will appear on the new ****,if its missin on one of them then there won't be any regarding to this date.

so now that i made myself clear (hopefully),any ideas ?

thanks,hummiz

Hi

First I did exactly what you told me,but it does not show the date column,it write other parameters which I don't really know where did it get from,so any idea ?

What does it 'write'?

the date on each file is not the same as the others,some of them are the same and some of them aren't.

Do the following:
You have 3 sets of data i.e.
Set1: date1, stock
Set2: date2, S&P500
Set3: date3, Auction bill

Select the first set, both columns - date and stock, and plot the XY chart. Next, go to the graph, right-click and select source data. Go to series tab, click on Add, and in the X, add the range for date2 and Y should have range for S&P500. Next repeat the process for the third set.

Mangesh

Originally Posted by mangesh_yadav

Hi

What does it 'write'?

Mangesh

it write numbers such as - 1000,2000,3000 on the buttom of te chart.

Originally Posted by mangesh_yadav

Do the following:
You have 3 sets of data i.e.
Set1: date1, stock
Set2: date2, S&P500
Set3: date3, Auction bill

Select the first set, both columns - date and stock, and plot the XY chart. Next, go to the graph, right-click and select source data. Go to series tab, click on Add, and in the X, add the range for date2 and Y should have range for S&P500. Next repeat the process for the third set.

Mangesh

im sorry but maybe i forgot to say that,before im getting this information to a chart i need to arrange it,the all 3 sets (6 columns together) to 1 sets which will contain only 4 columns - Date,stock,S&P500,Acution bill. and it will be shown only if every date in one set will apear on the two others.

after making this new sheet with the 4 columns i need to chart them
so did u understand me now ?
its just one step before ur answer
I hope u'll answer me soon as yesterday.

thank u,hummiz

Hi Hummiz,

Try the following:

Set1: Date1 and Stock in range A1:B4
Set2: Date2 and S&P in range C1:D5
Set3: Date3 and Auction in range E1:F6

The forst set is the smallest, and this is important in the followiung formulae. Use your smallest set in range A1:B4, or change the ranges in the formulae below.

Do the following.
Your smallest range is 4 rows long. So select 4 rows in column A say A10:A13. Enter the following formula in the first cell while the selection is still on:
=IF(ISNUMBER(SMALL(IF(--ISNUMBER(MATCH(A1:A4,$C$1:$C$5,0))*--ISNUMBER(MATCH(A1:A4,$E$1:$E$6,0)*A1:A4)=0,"",--ISNUMBER(MATCH(A1:A4,$C$1:$C$5,0))*--ISNUMBER(MATCH(A1:A4,$E$1:$E$6,0))*A1:A4),COUNT($A$1:$A$4)-(ROW($A$4)-ROW(A1:A4)))),SMALL(IF(--ISNUMBER(MATCH(A1:A4,$C$1:$C$5,0))*--ISNUMBER(MATCH(A1:A4,$E$1:$E$6,0)*A1:A4)=0,"",--ISNUMBER(MATCH(A1:A4,$C$1:$C$5,0))*--ISNUMBER(MATCH(A1:A4,$E$1:$E$6,0))*A1:A4),COUNT($A$1:$A$4)-(ROW($A$4)-ROW(A1:A4))),"")

confirm with control - shift - enter as this is an array formula.
This formula will enter the most common dates in an ascending order. Please change the format to suit yourself.

In cell B10 enter the formula:
=IF(ISNUMBER(VLOOKUP(A10,$A$1:$B$4,2,0)),VLOOKUP(A10,$A$1:$B$4,2,0),"")
and press enter. drag down to copy till end of values in the dates column in A. These are the Stock prices.

In cell C10 enter:
=IF(ISNUMBER(VLOOKUP(A10,$C$1:$D$5,2,0)),VLOOKUP(A10,$C$1:$D$5,2,0),"")
and copy down. These are S&{

In cell D10:
=IF(ISNUMBER(VLOOKUP(A10,$E$1:$F$6,2,0)),VLOOKUP(A10,$E$1:$F$6,2,0),"")
These are auctions.

Chart them using the XY plot

Mangesh