Calculate highest average balance during month

[jonrayworth]

Hi,

I have a spreadsheet listing all deposits made during the month to an account. I also have the Value & Maturity (Start and End) dates for each deposit.

I'm currently manually entering in the amount of each deposit for each day of the month that the deposit is valid. E.g. A deposit of 1m was made on the 12th, and matured on the 28th. Another deposit of 3m was made on the 19th, and matured on the 30th. Therefore, the highest balance at one time was 4m. I hope this makes sense...

There can be about 100 individual deposits to each account, so doing it manually isn't really an option. Does anyone know of a better (and faster!) way?

Thanks in advance.

[no dice]

Try the MAX function on the balance column. It will return the largest value (balance) in the selcted range.

[rylo]

Hi

See if the attached file gives you some ideas. You don't provide any real details of your structure, so I've just dummied something up.

HTH


rylo

[jonrayworth]

Thanks guys. Rylo, you're a star. That spreadsheet was exactly what I needed. You've saved many man-hours!