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


--
hummiz
------------------------------------------------------------------------
hummiz's Profile: http://www.excelforum.com/member.php...o&userid=23877
View this thread: http://www.excelforum.com/showthread...hreadid=375161

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375161

mangesh_yadav Wrote:
> 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


--
hummiz
------------------------------------------------------------------------
hummiz's Profile: http://www.excelforum.com/member.php...o&userid=23877
View this thread: http://www.excelforum.com/showthread...hreadid=375161

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375161

mangesh_yadav Wrote:
> Hi
>
> What does it 'write'?
>
> Mangesh

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


mangesh_yadav Wrote:
>
>
> 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

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

--
hummi
-----------------------------------------------------------------------
hummiz's Profile: http://www.excelforum.com/member.php...fo&userid=2387
View this thread: http://www.excelforum.com/showthread.php?threadid=37516

Hi Hummiz,

Try the following:

Set1: Date1 and Stock in range A1:B4
Set2: Date2 and S&P in range C15
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(A 10,$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(A 10,$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(A 10,$E$1:$F$6,2,0),"")
These are auctions.

Chart them using the XY plot

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37516