Correcting data in Excel

Last time I have described a theory about finding typos in a set of data in an Excel file without using spell checker e.g finding typos in accounts numbers. Let’s assume that in most cases our data is correct (often true) and we collected number of transactions made by our clients. The data might look like below.


To compute probability we will use two simple Excel functions COUNTIFS and COUNT.
Continue reading


Theory of probability in the office. How to correct typos in the data?

dt160106Previously I have shown how to use Excel and VBA language to automatically format financial statements or other documents. Today I will describe how to use theory of probability to correct typos in the data collected in our database e.g spreadsheets.

Surprised? Yes, we use theory of probability and statistics, completely useless piece of …. maths, to automatically find and correct typos made by our clients or colleagues.

– We don’t need that because we have autocorrection tools in our office software!
– Show me then, how to correct surnames or account numbers using these tools?
Oh… We need to wait when our customer will tell us about the mistake in his/her personal data…
And then you need to say sorry ;)
– What can I do instead?
– Let’s look at the example below …

Continue reading

Programming in the office. Why?

hard_job_payWhy programming might be important for people working in the office? We can work efficiently, better… we surely hear this propaganda. In practice… how to start when we haven’t got a break to learn during the day? One may say: learn at home in your free time! Easy…

Do we like our job to be engaged in a such a way? What we will do after we’ll improve our skills? Do we have other tasks or responsibilities? Do we believe that we will be appreciated by our company? Many questions and doubts…

However, we may look at these questions in a different way. Let’s imagine…. we are working with the spreadsheets. We have to prepare a financial statement to our customers. Many financial statements… Collecting the data¬† from number of databases, computing a financial balance…. and merging cells, coloring rows in a spreadsheet manually… Then we wake up with a well designed tool that is able to do all these things automatically ;)

Will you like your job more armed with this tool? How many your ideas were rejected due to the lack of time? How many time will be saved thanks to your engagement and programming skills?

Dream is only a dream but spreadsheet that colors rows and merges cells might be quite real ;) Click! Good point to start is googling for “how automatically …. in spreadsheet”. Your turn to learn programming :)