AMB (w/o every date listed)

[twofordiving]

Hello!

I am at a loss in trying to figure out the formula for this issue. I am trying to calculate the AMB for a Line of Credit account for my business. The problem is not every date from each month is listed within the table. Only dates when the principal balance changes (from payments or withdrawals). I've attached the excel spreadsheet and was wondering if anyone can help me with this formula?

I need the dates from column A and the Principal balance from column J. The average monthly calculation needs to be put under Column O. They are all highlighted in gold.


Please let me know if you need me to clarify anything else. You will be saving my life if you figure this out. Thank you!!!!

[FlameRetired]

Hello twofordiving. Welcome to the forum.

If I understand the objective clearly try this in O9 and copy down.

Please Login or Register  to view this content.

[twofordiving]

Thank you for the help. Unfortunately, I got these same results and it wasn't correct. I wonder if this has to do with not having the starting balance at the start of each month? Some lines dont have values for the start of each month.

[FlameRetired]

If you know they are incorrect this seems to indicate you know what the correct figures are. If so please post those in your example.

[twofordiving]

I apologize. Check this attachment below I have an example and the total it should be is highlighted in yellow.

[FlameRetired]

Thank you. That helped.

In the attached I extended the date range in column O. In column P this formula to list all ending balances even for dates not listed. It matched the data you added in your last upload for Jan/23 dates.

Please Login or Register  to view this content.

Then reference that for this formula in O9 copied down.

Please Login or Register  to view this content.

[twofordiving]

I appreciate your help but we can't use the table all written out like that. I need to somehow get that number just from the data in the table with the dates not all there. We are trying to avoid having to list everyday out of the month. No chance you can get that answer just from original data?

[FlameRetired]

Yes. Same upload as in Post #6 with this formula in column M for comparison.

Please Login or Register  to view this content.

You may wish to create Dynamic Named Ranges for your dates and ending balances as when you extend this formula the averaged balance continue to return values from the last entry (currently in Feb/23).

[twofordiving]

You are seriously awesome. THANK YOU!!!

[FlameRetired]

Thank you. You are very welcome.

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

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.