pivot time series with two different categories

[labogola]

I have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.

Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.


I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".pivotland.xlsx

[etaf]

try changing the layout
so bid/offer is across the columns
and date is in the rows
and then count the dates

see attached modification to your pivot chart setup

[labogola]

if I wanted to create a chart a custom indicator over time, e.g. Bid / (Bid+Offer) would I need to add that data to the original data table? I'd like to do that but it'll be an issue as that table is constantly being updated. Is my best bet using an if function?

i.e. Columns A to J are being used up and updated (including the bid/offer column).

[labogola]

Thank you. That helps. Unfortunately I realized charting bid/offer categories as separate is not very informative.

[etaf]

if I wanted to create a chart a custom indicator over time, e.g. Bid / (Bid+Offer) would I need to add that data to the original data table? I'd like to do that but it'll be an issue as that table is constantly being updated. Is my best bet using an if function?

if you set the data up with a table and then use a formula as you ad more data to the table the formula should be carried down the column

you would need to group by the date , if you wanted to calculate
Bid / (Bid+Offer)
each day

you could probably separate the bid and offer into two columns and then use a formula / calculated field in the pivot table to do that
is that what you are after

[labogola]

No. I'll make a new post for it

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.