**But first a quick note: **Congratulations to all the entrants. I have learned a lot just by playing with your files and exploring them. Thank you so much for taking time to share your knowledge with all of us.

*Click on their names to see original entries.*

- Two first prizes of US$ 100 Amazon gift cards go to
**Chandeep**&**MF Wong** - I have decided to give three more consolation prizes $25 Amazon Gift Cards to
**Pinank, Miguel & Gerard.**

Congratulations to all of you.

]]>

Tada!!!

Biker on a hill!?! Don’t worry, I didn’t fall down on a descent and lose my brain. I am talking about an Excel chart to visualize target vs. actual progress on a time line with biker on a hill analogy. See the chart, you will know:

Looks interesting? Read on to learn how to create this in Excel.

The *biker on a hill *chart is suitable when you have a huge target that should be achieved in several days, each with individual target. So input data looks like this:

Let’s say this data is in a table named **progress**. As you can guess, first three columns are inputs. Last column is calculated with a simple SUM formula to get cumulative target values. The formula used here is

=SUM(E3,[@Target])

Where E3 refers to the cell above first row.

The hill is a simple X-Y chart with progress[Date] as X and progress[Target-Cumulative] as Y.

The tricky part is finding biker co-ordinates. We need 2 sets of values.

- Actual biker position based on the amount of work completed.
- Target biker position based on the amount of work that
completed.**should have been**

Let’s understand the math behind this.

**Actual biker position:**

Y value (actual completed): This is simply SUM(progress[Completed])

X value (corresponding date): Now this is tricky. We need to find what date corresponds to the actual progress made based on the set targets. for this we need to find several things:

**Corresponding row #:**Using MATCH(), we find out what value in the cumulative target column matches the progress completed so far.- We will then find two dates between which the progress completed falls between, using MATCH formulas.
- Finally, we will interpolate the corresponding date between these two dates using simple arithmetic.

**Target biker position:**

Y value (target): Target as of latest date, using either VLOOKUP or INDEX+MATCH

X value (date): this is simply date in **corresponding row # **that is calculated above.

**Step 1: Create an XY chart for the hill**

Setup an XY chart where X=progress[Date] and Y=progress[Target-Cumulative]. We get this.

**Step 2: Add target & actual bikers**

Add two more series to the chart. Target biker and actual biker using the X&Y values calculated above. We get this.

**Step 3: Replace the dots with biker images**

This is easy. Download a clip art image of cyclist from internet. Paste it on your Excel workbook. Remove any background. Rotate the biker image by 30° or so. As this is not a real life biker on hill, we can afford 30° gradients.

Once you have new rotated biker, reduce the image size if necessary and clone it.

Change colors using format image > Color options (see here for detail).

**Essentially, go from the image on left to right.**

Now that you have biker images, replace the dots with bikers using following instructions.

- Select biker image and copy (Ctrl+C)
- Click on the dot in your chart
- Press Ctrl+V to paste image
- Viola, your chart now shows bikers instead of dots for actual and target values.

At this stage our chart looks like this.

**Step 4: Move the bikers up**

Because Excel places the dot right on the line, the biker image too will be centered on it. So instead of looking a *biker on hill, *our chart looks like the biker is buried half in the hill. Not good, whether you are a fictional or real biker. So let’s pull the bikers.

We can do this by simply adding an offset value to the Y values. A value of 7 should work. But you can tweak this depending on your chart / image sizes.

Once we fix the calculations, our chart looks like this.

Bikers are known to pimp their rides with all sorts of doodads. We can show similar enthusiasm for our biker on a hill chart and add few more details. Here is one version after adding * information about current progress status and forecast date of completion*.

The math for this is quite boring and simple. So I leave it to your imagination.

**Click here to download biker on hill chart**. Play with input data to move the biker towards target. Examine calculation section or chart to learn more.

Time for a confession. The biker on a hill chart idea isn’t mine. I got this from GraH, one of our readers. He left a comment on a recent blog post and I liked the idea. So I wrote this blog post explaining how we can all create a biker on hill chart in Excel.

Hui rules! But nevertheless, the creativity on this blog and the contribution of the bloggers are inspiring. And really enabling people to be aweSUM in XL, like you say.

I followed a training on Excel Dashboards in 2015 and your site was highly recommended by our cool trainer.

I became aware that XL can-do much more. Ingredients are a little imagination, dare to experiment and knowledge on how to combine techniques/functions.

In short within 2 weeks I will give a 1 hour XL awareness training in my company during open training week. I just sent a teaser with stuff I found here and on other XL-guru’s sites to my HR department. Within the next 5 minutes my proposition was approved. The reply was “Excellent idea!”

And the funny thing is that I found an XL soul-mate only a few seats away. Now we make each other crazy with challenges and/or things we learn about XL. The very first thing we made for a manager was. Depending on current status, different motivational talk appears in the title. The manager could not believe we just made a simple chart.a biker (representing his team) climbing a mounting (of work) towards the finish (the volume to reach at end of day)

**So thanks GraH for the cool idea**.

I prefer conditional formatting icons and thermometer charts for budget / target vs. actual progress charts. Sometimes I use a bullet chart or variations of thermometer charts too. I have also used burndown charts (same concept as biker on hill charts). I like the biker on hill chart and may use it for some of my upcoming work.

**What about you? **What charts do you use to depict target vs. actual progress? How do you like biker on hill chart? Please share your thoughts and suggestions in the comments section.

This week I have taken that technique and added some high-performance steroids.

I have allowed for three (High, Medium and Low) Indicator arrows, which can be in any order

I am not going to go through how to construct it, as it is exactly the same as last weeks post, except that it needs a few helper cells.

It can be applied to both Column Charts as above or Bar Charts as below:

The other thing to remember is that you can use any Shape as the indicators.

Please download the sample file here, pull it apart and see what you think.

]]>

“How do I convert a Roman Numeral to a Number eg: MMMCCCLVII to 3357”

User Xlstime presented the solution of:

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

Today we are going to look at how and why that simple formula works

As always at Formula Forensics you can follow along using a sample file: Download Sample File

Excel has a Roman function wherein =Roman(3357, 0) will return **MMMCCCLVII**

I knew there was no such reverse Roman function prior to Excel 2013 and so my initial thought was to look at a VBA Solution.

See notes on the Arabic Excel function at the end of the post.

However Xlstime presented =MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

How does this work?

lets start by pulling it apart from the inside out

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

The Indirect Function simply takes its inputs and converts them to a Range, in this case 1:3999.

We will study why 3999 later

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

The next function working out is Row()

Excel will convert the function ROW(INDIRECT(“1:3999”)) to an array of Row Numbers

={1;2;3;4;5;6;7;8;9;10;11; …. 3995;3996;3997;3998;3999}

You can see this if you goto cell D7 in the Sample File, press F2 and then F9

I limited the numbers to 300 as Excel cannot display more than 8,192 digits

Stepping out one more function:

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

The Roman() function converts its inputs into Roman Numbers

eg: Roman(58) will return LVIII

But as we are feeding it an array of numbers from 1 to 3999 Excel handles all these and converts them to an Array of Roman Numbers

Goto D9 in the sample file =ROMAN(ROW(INDIRECT(“1:300”))) press F2 and then F9

Excel returns an array of roman numbers

={“I”;”II”;”III”;”IV”;”V”;”VI”;”VII”;”VIII”; … “CCXCVI”;”CCXCVII”;”CCXCVIII”;”CCXCIX”;”CCC”}

*We have limited the example to 300 as Excel cannot display more than 8,192 characters when processing a Function using F9. *

Stepping out one more function

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

The Excel Index() function is taking the Array of Roman Numerals and Converting it into a single Column array

This isn’t technically needed but it simplifies the solution

If you goto cell D11 in the sample file =INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0) press F2 and then F9

Excel returns an array of roman numbers

={“I”;”II”;”III”;”IV”;”V”;”VI”;”VII”;”VIII”; … “CCXCVI”;”CCXCVII”;”CCXCVIII”;”CCXCIX”;”CCC”}

This is exactly the same as the previous output from the Roman() function above, Except that it is now a Single Vertical Array. This is important for the next function.

Stepping out one more function

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT(“1:3999”))),0),0)

We can see here that the Array of Roman Numerals is now being fed into a Match() function.

Match uses the Syntax **=Match(Lookup value, Lookup Array, Match Type) **

Match returns the position of the Lookup value within the array

So in our example

The Lookup value is A2 or our Roman Numeral MMMCCCLVII

The Lookup Array is an array of Roman Numerals from 1 to 3999

={“I”;”II”;”III”;”IV”;”V”;”VI”;”VII”;”VIII”; … “MMMCMXCV”; “MMMCMXCVI”; “MMMCMXCVII”; “MMMCMXCVIII”; “MMMCMXCIX”}

and the Match Type is 0 or an exact Match

So the Match function will lookup the value MMMCCCLVII in the array and find it in position number 3357, which happens to correspond to the Number of the Roman Numeral and Return 3357 as the result.

The Excel Roman() function is limited to numbers up to 3999

Why 3999?

Because in Roman Numerals there is no Letter for 5,000 and 4,000 would be shown as M before the Letter for 5,000.

If you goto D17 in the sample file you will see the formula:

=MATCH(A2,ROMAN(ROW(INDIRECT(“1:3999”))),0)

It is the same formula as above but without the Index() function

You will see that it is returning a #VALUE! error

If you edit the formula with F2 and then press F9 to process the function you will see it now shows 3357

What s happening here?

Pressing F9 is the same as Array Entering the Function

so if you edit the function pressing F2 and now Array Enter the function by pressing Ctrl+Shift+Enter, excel now returns 3357

The Index() function puts a wrapper around the array for processing by the Match() function and so Array Entering is avoided.

Most array formulas that require Ctrl+Shift+Enter can be rewritten incorporating an INDEX wrapper and will not require the Ctrl+Shift+Enter confirmation.

In 2013, Microsoft introduced the Arabic function to Excel

To use simply use =Arabic(A2) or =Arabic(“MMMCCCLVII”)

You can read about the Syntax of the function in the Excel Help.

You can download a copy of the above file and follow along, Download Sample File.

Can you solve the problem another way ?

Post your solutions in the comments below.

The Formula Forensics Series contains a wealth of useful solutions and information specifically about how Normal Formula and specifically Array Formula work.

You can learn more about how to pull Excel Formulas apart in the following posts: http://chandoo.org/wp/formula-forensics-homepage/

If you have a formula and you want to understand how it works contact Hui and it may be featured in future posts.

]]>

- Build a model to estimate profit per given number of copiers & demand values
- Find the mix of copiers & demand values that can make maximum profit for Sara (copiers - 1 to 6, demand - 500 to 2000)

*Thanks to Caroline who posted this problem.*

Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.

- Build a model to estimate profit per given number of copiers & demand values
- Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)

We can use simple formulas to build a break-even model for this problem. We can then enhance it by using 2-way data tables to calculate optimum mix of copier & demand values.

I created a video explaining the process. Check it out below. You can also watch this on our YouTube Channel.

**Click here to download the workbook with break even & what-if analysis model**. Play with various formulas to learn more.

Check out below resources to learn more about various analysis and modeling techniques using Excel.

**Resources:**

**Case studies:**

- Cost benefit analysis – Which bulb is best?
- Building a retirement calculator using Excel
- Calculating CAGR using Excel
- Multiple scenarios using slicers

**Courses:**

**Do you think our model is adequate?** If not, what else would you add to it? How would you enhance it? Please share your suggestions and implementations in the comments area. *Shower suggestions on Sara so she succeeds. Go.*

Which I did.

This post will describe how to tackle this chart step by step.

You can follow along using some sample data: Download Sample File

To produce this chart we are going to use an Excel Stacked Bar chart with two series of data

The first Series will be for the Colored Bars

The second series is for the Arrow and the gap to the left of the arrow

The data required is shown in the above table

The Arrow Value is an input and is the value the Arrow will point to

The data is the values for the colored bars

The Arrow is two calculations that setup the Arrow, the 58 is the offset from zero to the left side of the arrow

The 4 is the width of the arrow. That is the arrow will point to 60 = 58 + 4 /2

The Cumulative Data is required for the Legend

In **Excel 2016**

Select the range C3:G4 and goto Insert Chart

Select Bar, Stacked Bar

**In Excel 2010**

Select the range C3:G4 and goto Insert Chart

Select Bar, Stacked Bar

Now with the Chart selected goto the Chart Tools, Design Tab

Click on the Switch Row/Column Tab

**Excel All Versions**

You should now have a chart like:

Bar 1 is the data and Bar 2 will become the arrow

We don’t need the Charts Title, Legend, Grid Lines or Axis, so select each and press Delete

Next we will add an arrow

The Arrow will be placed as a Fill in the Upper Orange Bar

Select a Blank Cell eg: I3

Then goto the Insert, Shapes Menu and select an Isosceles Triangle

Fill the Arrow with what ever color you want and drag the Handle down so that the arrow points down

To insert the arrow, select the Arrow and press Copy (Ctrl+C)

Now select the Chart and click on the Upper Orange Bar, click on it again until it is the only Bar with Handles Showing, the press Paste (Ctrl+V)

Now select the Upper Blue Bar and set its fill and outline to None

Right Click on any Bar and select Format Data Series

Set the Series Overlap to 100% and set the Gap Width to 0%

Now click on the chart, just above the Blue Bar

When the Resize Handles appear, drag them to resize the chart so that the gap between the Top and Bottom Bars is none

We can now add the labels

Select the Orange Bar and then Select it again until it is the only Bar with Handles

Right Click on it and Add Data Label

Now click on the new Data Label and click on it again until the Handles change as shown below

Now in the Formula Bar enter =Chandoo.org!$C$5 and apply

Right click on the Data Label and select Format Data Labels

Set the Label Position to Inside Base

Now repeat for each of the other Bars

and Finally add a Label to the arrow linking it to cell C2. You have to manually drag the Label to above the arrow.

Now that you know how to make a Bar Chart with Indicator Arrow, it should take you less than a minute to copy the bar chart, convert it to a Column chart and reformat it to a Column Chart with arrow as shown below

I hope you enjoyed the above tutorial

]]>

**Tell me about an analysis problem that you couldn’t solve with Excel?**

It can be because you didn’t know how to solve the problem or Excel isn’t the tool for it or any other reason.

Go ahead and speak up. Post your tricky analysis problems in the comments section.

]]>