MIN/ MAX/ AVERAGE with array formula

Which ones is the champion?

🏇🏇🏇🏇🏇 Congrats Tina to be the best!

screen shot 2019-01-23 at 20.31.26

Let’s illustrate Excel array formula with a horse racing game as the following table.screen shot 2019-01-23 at 19.44.33

Data area in column Horse Name (table raw data) is named as horsename (F2:F16) and column Duration is named as duration (H2:H16).

From provided data, analysis result should be delivered as below. Data area in column Horse Name (table result) is named as nameresult (A2:A6).

Continue reading

Interactive Sum in Excel

You need a sum value that should be changed regarding to your filter selections. So, let’s do it!

Please have a look at the following example! The table figures out sales volume and revenue of 5 products in 3 shops.

screen shot 2019-01-19 at 23.19.48

As usual requirement, filter function would be used (by product and/or shop) to obtain desired sales volume and revenue. However, with SUM function, those values cannot be changed based on filter selections. So, how to make it interactive?

Continue reading

Gamification in the age of Industry 4.0 (Part 1)

arcade_daze_full

Source: Google Image

Industry 4.0 has been become a popular topic around the world for several years. Together with other mega trends such as Artificial Intelligence, Augemented Reality, Big Data as well as Cloud Computing, Gamification has gained its currency in the age of Industry 4.0.

What is Gamification?

In simple words, gamification is a process of applying game mechanics into an existing non-gaming context in order to motivation participation and improve engagement from users. So games are now not only for kids; games are now for everyone and can be applied in anything. An simple example is that collecting a stamp every time we buy a coffee and then 10 stamps for a free drink. It is like in a game – completing a level to get a reward.

Continue reading

How to reach the target? Excel can help you

You have a list of objectives ranging from study results, personal finance to sales revenue; and you try your best monthly, even daily to accomplish them. Sometimes you wonder which number I need to reach the goal. Don’t worry, you are not alone; and fortunately there is an amazing Excel function supporting you on the way to touch the target. Please note that here I am using Microsoft Office Excel 2016 for Windows.

Imagine that you are an owner of a mini store selling cookies, chocolate and oranges. Based on customer feedback, you will sell a new product which is apple in the next month. Your target is to get 5,000 Euro of profit in the next month. Have a look, there is a table like this.

Goal Seek 1

Data of a mini store

History data is useful to estimate sales revenue of cookies, chocolate and oranges; however how big sales revenue of apples should be? That is your concern.

Just relax and let Excel help you. The function I would like to show here named Goal Seek. It is in tab Data, area Forecast and under option What-If Analysis.

Goal Seek 2

Function Goal Seek

What you need to do is simply selecting Goal Seek. Then just fill in the box.

  • Set cell: is the cell contains the target. Here is cell D6.
  • To value: is the value of the target cell. Here we need to fill in 5000.
  • By changing cell: is the cell whose value we need to figure out. The “By changing cell” here is B4 that is sales revenue of apples.
Goal Seek 3

Fill in those boxes

What’s next? Simply click OK and see how amazing it is.

Goal Seek 4

It turns out the result

It finally returns that sales revenue of apples should be 3413.33 Euro.

Note

There must be a relationship between the Set cell and By changing cell, otherwise the function cannot work well. In this example, they are linked by these two formulas:

Profit = Revenue * Profit Margin

Target = sum of Profit

Hopefully, you can apply this function to both your life and work. Enjoy it, Goal Seek!

Welcome to Industry 4.0

Internet of Things 7

Source: Google Image

The Fourth Revolution of industrial manufacturing has set its foot in our current life on the global scale. Before discovering the 4th revolution, let’s quickly look back on the previous revolutions. Thanks to the invention of steam engine, the First Revolution was known as the era of mechanization; while electric-powered assembly line and mass production solely performed in the second ones. Then the Third Revolution brought automation and information technology into factories. And now, what is new in the fourth? The Industry 4.0 hopefully is able to deliver these latest technologies like Artificial Intelligence (AI), Big Data and Internet of Things.

Throwing back to 2013 when the term “Industrie 4.0” was firstly mentioned in a project of high-tech strategy of Germany, this plan aimed to computerize the manufacturing industry with much less even without involvement of human. As Hinks (2015) mentions, Germany then has invested about €200 million on this research across academia, business and government. While in the US, manufacturers, suppliers, technology firms, government agencies, universities and laboratories have collaborated with the common goal to construct an open and smart manufacturing platform for industrial-networked information applications. This platform ambitiously allows manufacturing firms of all sizes to access and utilize modeling and analytical technologies that can be customised to meet their needs.

Continue reading

Are you ready to move ERP to the Cloud?

In recent few years, cloud computing has been dramatically developed and become the area of focus to general public ranging from normal users to businesses. And the phrase “To cloud or not to cloud” is considered as a familiar question in any IT strategy these days. Similarly, not only is “cloud” no longer a strange term for enterprise applications, but also has been the prominent trend.

entenda-como-a-computacao-nas-nuvens-aumenta-a-competitividade-do-seu-cursinho-1

Source: Google Image

What is cloud computing?

As a claim by Berry, cloud computing could be understood as the delivery of computing services over a network that is a proprietary network or even the Internet. Those services are mainly infrastructure, platforms and applications. In simple words, Griffith (2016) states that the cloud here is a metaphor of the Internet; and cloud computing refers to store and access data or programs through the Internet instead of your device’s hard drive.

cloudcomputing

Source: Google Image

A typical example of cloud computing for normal users is Google Drive where your data is stored and cloud applications such as Google Docs and Google Sheets can be also found online. Moreover, the drive can be accessed by various types of device from PC, tablet or smartphone. Another recent example is Office Online offering web-based version of Word, Excel, Power Point and One Note, which means users can access these applications via web browser without installing anything.

Continue reading

Basics of UX Design and Usability (Part 3)

quote-architecture-begins-where-engineering-ends-walter-gropius-11-83-99

Source: Google Image

User Interfaces Are “Visualized Requirements”

It is usually said that it is possible to cook bad food from good ingredients but it is not possible to cook good food from bad ingredients. That is a good metaphor in this case even with excellent visual designers and front-end developers, high quality user interfaces cannot be released with poor requirements. So well-defined requirements can be supposed by applying suitable and effective methods and techniques in the stage of gathering and documenting requirements (that will be in other coming posts; if you are interested, you should looking for business analysis techniques and requirement management tips).

Creating fancy user interfaces cannot ensure the success of products because the ultimate objective is to build user interfaces best meeting requirements. Hence, it is highly recommended that we should focus more on outcomes (values) rather than outputs (deliverables). It seems like a popular said “doing the right thing is always more important than doing the thing right”. User interfaces are also requirements in visualized form, so in order to reach the objective, both business analysts and UX designers should well understand requirements.

5 main types of requirements

Continue reading