Which ones is the champion?
🏇🏇🏇🏇🏇 Congrats Tina to be the best!
Let’s illustrate Excel array formula with a horse racing game as the following table.
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).
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.
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?
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.
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.
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.
Fill in those boxes
What’s next? Simply click OK and see how amazing it is.
It turns out the result
It finally returns that sales revenue of apples should be 3413.33 Euro.
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!