2 question for Excel champs ;)

  • Thread starter Thread starter hummiz
  • Start date Start date
H

hummiz

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 ?
Click to expand...

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.
Click to expand...

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


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
shit,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 ?
Click to expand...

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.
Click to expand...

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
 
mangesh_yadav said:
Hi

What does it 'write'?

Mangesh
Click to expand...

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


mangesh_yadav said:
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 X
chart. Next, go to the graph, right-click and select source data. Go t
series tab, click on Add, and in the X, add the range for date2 and
should have range for S&P500. Next repeat the process for the thir
set.

Mangesh
Click to expand...

im sorry but maybe i forgot to say that,before im getting thi
information to a chart i need to arrange it,the all 3 sets (6 column
together) to 1 sets which will contain only 4 columns
Date,stock,S&P500,Acution bill. and it will be shown only if every dat
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,hummi
 
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 followiun
formulae. Use your smallest set in range A1:B4, or change the ranges i
the formulae below.

Do the following.
Your smallest range is 4 rows long. So select 4 rows in column A sa
A10:A13. Enter the following formula in the first cell while th
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 date
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

Manges
 
Back
Top