How to count number of cells (precedents) in a formula for one cell.

[CTaylor]

Company A has unpaid invoices. I am calculating interest on said unpaid invoices. Interest formula is written as seen in columns X and Y on attached sheet. I would like to find a way to count how many invoices (precedents) are being calculated in the amount for column X.

The invoice that the upper management wants includes how many invoices are unpaid for each interest charge. Aside from manually counting and manually entering the number in column W, I am unable to find any formula on the interwebs to do this, i.e. “How many cells (precedents) are referenced in the formula I’ve already written?” And as you know, this leaves considerable room for error. I’m all about automation when possible!

I do not have any experience with VBA, but I am willing to learn if someone is willing to help.

Hoping someone can help, thank you!

[mdmackillop]

If you're just looking at column X then enter = INV(X9) etc. where

Function Inv(data)
Inv = UBound(Split(data.Formula, "+")) + 1
End Function

[SamT]

An approximation can be gotten by counting the Invoices and subtracting the Count of Checks received. IMO, that is the best you are going to get, since, IMO, that billing/accounting method is FUBAR.

Another reason an approximate count is the best you can get is that the question is predicated on the baseless assumption that the Payer will always pay by invoice. Assume, for the sake of argument that the payer owes $10,000 from a number of invoices and he pays a single check of $8,000, but no total of any invoices is even near $8,000.

Why is the system FUBAR? Note the Formulas in Column X, The amount in Cell V8 is summed Fibonaccially 13+n times in Cell X31! In fact many amounts are summed many times in column X.

A couple of simple calculations shows that you are charging a cumulative 17% interest using that system.

I looked at your Ageing Column. Try this Formula:
=IF(AND(AC2="",E2+5<$AR$1),$AR$1-E2,"") Note that the +5 presumes that payments aren't Aged for 5 days after the due date; adjust to suit.
And Custom Format the Cells like
### "Days"; (Ampersand required) Note that the "Days" displayed in the Cell is NOT part of the Cell's Value. You can use the cell value in the 0-30, 31-60, 61-90, >90 column formulas.

[CTaylor]

This response seems like the most probable solution for me. Problem is, I have no idea how to use macros/vba etc. I suppose I need to do more research...

[SamT]

Well, Oh Grand Master,
I wonder who the OP is responding to?

Probably you, since I critiqued his Spreadsheet.

[CTaylor]

No no no. I'm sorry. I didn't take offense to the critique. I appreciate any and all feedback. Yes, my original response was to Grand Master. He makes it seem so simple! My problem is that I'm not sure how to get that code applied to my live spreadsheet so I am able to use that oh so simple "= INV(X9)" solution.

Well, Oh Grand Master,
I wonder who the OP is responding to?

Probably you, since I critiqued his Spreadsheet.

[CTaylor]

*her* spreadsheet

Well, Oh Grand Master,
I wonder who the OP is responding to?

Probably you, since I critiqued his Spreadsheet.

[mdmackillop]

Hi
The workbook I posted above contains the code with the function in Column W. Let me know if you need more.

[SamT]

Well, Ma'am,

Open VBA, Make sure the Project Explorer is Viewable. Right Click on "ThisWorkbook" under the Workbook's actual name and select Insert >> Module.

Paste his code into that module and the User Defined Function will be available throughout that Workbook for use in any Cells Formula.

"X9" stands for the Cell you want the count of precedents of.

[CTaylor]

I can't thank you guys enough!! This finally worked with a little more explanation and use of the original code. You guys have saved the day! You are now stuck with me. You can bet I'll be back for more help as needed. Thanks again!!