• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract digits before the decimal point.

Glad it's working for you!


I'm on my way to bed, but a hint with the ROW() thing..

The ROW() function retrieves the row number of a cell, or creates an array of row numbers if given a range of cells. ROW($1:$6) refers to the range of all the cells in rows 1 to 6. It will create the array {1,2,3,4,5,6} of the corresponding row numbers -- it will not refer to the data in those rows. (I think COLUMN($A:$F) would also give you {1,2,3,4,5,6}) Then the formula does it's calculation substituting 1 for ROW(..), 2 for ROW(..), 3, 4, 5, and 6 for ROW(..)... and then takes the SUM() of those results for the final result. OK, I guess that's more than a hint :)


It's a neat array formula trick I just recently caught onto myself. It essentially causes your formula to do a loop, kind of like a FOR/NEXT loop in BASIC.


Asa
 
It was the trick of using SumProduct with the internal array functions and Cntrl-Shift-Enter that finally did it. I tried several other array formula combinations that almost worked, several tense minutes with the Evaluate Formula button, but no joy.


Sorry about the decimal thing. I was looking at the concept of "round dollar amounts" and didn't mentally link that with cents being present. Worked out in the end.
 
Mike, I would have expected your formula to work without array-entering it. It seems like SUMPRODUCT is not able to treat the range 1:6's in your formula as an array, must have hit on some peculiarity of sumproduct formula evaluation. Since you are calculating no products for your formula, SUM() is behaving exactly the same as using SUMPRODUCT (with Ctrl-Shift-Enter) in as far as I can see.


Out of curiosity, Mike, do you see any reason why this isn't creating an array? I don't think it's of specific interest to persol, but I hate writing a formula that makes perfect sense to me, and then not being able to get the result I expect. formula works if entered in multiple cells substituting the values that should be in that array, but doesn't work when array entered:

Code: 
{ =MAX(GCD(TRUNC(ABS(A1)),10^(ROW($1:$9)-1))) }


Asa
 
Figure out the problem in my GCD formula. GCD can accept an array argument, so is resolving the array before MAX. I wanted the array to survive the GCD call and have the set of GCDs evaluated by max, rather than one GCD for a set of numbers.


Code: 
=MAX(GCD(TRUNC(ABS(A3)),10^0),GCD(TRUNC(ABS(A3)),10^1),GCD(TRUNC(ABS(A3)),10^2),GCD(TRUNC(ABS(A3)),10^3),GCD(TRUNC(ABS(A3)),10^4),GCD(TRUNC(ABS(A3)),10^5),GCD(TRUNC(ABS(A3)),10^6),GCD(TRUNC(ABS(A3)),10^7),GCD(TRUNC(ABS(A3)),10^8),GCD(TRUNC(ABS(A3)),10^9))


Works I think, but not elegant :)


No array entry required.


Numbers maybe interesting to accountants, probably number of zeroes more interesting to management.


I'm neither, and it's interesting to me, but it just possible I don't really understand it.


Asa
 
Hello Asa,


I am not in the finance field but I have gone mad with the topic of digit analysis. I came accross benford Law (if you have never heard of, take a read here: http://www.uic.edu/classes/actg/actg593/Readings/Auditing/The-Effective-Use-Of-Benford's-Law-To-Assist-In-Detecting-Fraud-In-Accounting-Data.pdf).


Basically, all numbers are supposed to act in an expected manner where digits like number 1s will show up more frequently than 3s 6s, or even 8s or 9s. So, when I started reading all reports coming my way I started applying Benford and started becoming curious about it. Through my experiences I delivered to my boss reports that helped us correct inventory balances, we found an employee falsifying expense receipt records, and purchases being circumvented. Imagine that you went on a business trip and upon your return you submitted your expense report and your boss trusted you that no fake receipt was being submitted. Most employees go through that routine, however, when employees want to circumvent the (say $25 limit) expense report requirement for receipts of $25 or more, most would be dishonest employees will try to get "rich" by submitting expense amounts closer to that limit … say $24.01 all the way to $24.99 so that the accounting folks or their bosses will not question. If that is the case, Benford will note that these amounts were not expected and that are abnormal, so someone like me looks into more detail at those abnormal amounts and later discover irregularities or even fraud. Over a year ago I read the Wall Street journal on line, the IRS uses this technique to find frauds, go figure.


Are you still awake?


Ok, good, i thought I lost you with this dry stuff. So, with my data file, I am onto the purchasing data where the zeros have no major influence in the Benford Law, but they seem to do a lot when I look at the amounts after the period. I am sure that IRS or universities have fancy programs to do just the thing I am doing but with Excel - I am doing it cheap and faster. Imagine that you need approvals for purchases that have various escalation approval levels (as is the case with my employer) but employees decide to purchase these goodies with out the approval from all managers required to be involved in the process, these transaction will then be split in various amounts resulting in amounts that will fall outside Benford law expected digit behavior. Well I have been using a combination of graph charts, data stratification and Benford to help managers understand that there are problems following procedures and intentional activity (such as vendor collusion, kickbacks, etc).


Yea, it gets better than that but I am sure that by now you are snoring. Sorry, I am going mad with this thing about digit analysis with Excel. And, Chandoo is make it more difficult for me because I can not get enough ideas to present data and charts.


Alright Asa, stay good, and once again, thank you so much for the support. I am very happy with my fancy formula.


Persol
 
Thanks for sharing that info! I've just skimmed that paper thus far.. but I am very interested in statistical analysis in general, so you are certainly not boring me.


The formula Benford put forth is fairly simple to duplicate in Excel and we could even put together another array formula, I think, that would rate an entire amount for comformity to Benford's law. Not sure in what ways you've applied it so far.. but might be fun to put a unified formula together that calculates it for each digit and then factors them up for a unified analysis.


Asa
 
You must log in or register to reply here.
Back
Top