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

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!