Simple excel with Dates to show END OF MONTH balances

[Allilue]

HI,
I've got a really simple table showing Dates and Amounts.

MONTH DATE AMOUNT BALANCE
Jan 1/3/2013 $50 $50
Jan 1/5/2013 $100 $150
Jan 1/23/2013 $40 $190
Feb 2/6/2013 $10 $200
Feb 2/18/2013 $10 $210
Mar 3/4/2013 $100 $310
Mar 3/15/2013 $50 $360
Mar 3/29/2013 $60 $420

How can I make a chart to show the ending balances for each month? So ideally this would be a bar chart with Months as horizontal so Jan $190, Feb $210 and Mar $420. Seems this should be easy but I can't seem to get the last Balance of each month to show.
Thanks,
Allison

 

[SkipVought]

Hi,

Get to know how to use the PivotTable Wizard. HUGELY beneficial tool!!! You can generate a chart in less than one minute!

Data > Tables > PivotTable

Drag the DATE into the ROW area and the BALANCE into the DATA area. Right click the DATE and select GROUP and group by year and month. Right click the BALANCE and select MAX.

Then select Pivot Chart in the Ribbon. I can't remember where and I'm on my mobile device.

Good luck.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Allilue]

Actually I did try that...doesn't selecting MAX in the Balances section output the highest value and not the value for the latest day for each month?

 

[xlbo]

I guess if you assume balances will only increase then it is a good enough proxy

If not, you will need a formula to tag the last day of the month (that you have available)

You can create this using an array formula:

=IF(B2=MAX(IF(MONTH($B$2:$B$9)=MONTH(B2),IF(YEAR($B$2:$B$9)=YEAR(B2),$B$2:$B$9))),1,0)

Where B2:B9 is the range of cells to be evaluated

You can add this field to your pivot table in the page field position and use it to filter for only those rows that are teh end dates for the month

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question