## E90E50fx

### E90E50fx... only formulas

 Welcome to the E90E50fx site!This blog-site is a collection of articles about Excel. We share here our ideas, discoveries and some teaching materials about Excel related topics. The posts here are focused on formula-based solution. You can find lot of charts built with formulas.If you are interested in other Excel realated topics, you can check out the E90E50 site or take a look at our E90E50charts collection.Please respect the work of the authors. All of the publications are our own work. Feel free to use it, share it - but please always mention the source.Have fun, The FrankensTeam

#### How to sum the product of matrix or array rows?

posted Mar 12, 2014, 3:17 AM by Krisztina Szabó   [ updated Mar 12, 2014, 3:20 AM ]

 Freddy: Aren't there any lights in this place? [...] by The FrankensTeam This short post is just to share a great mathematical trick associated with an excel formula. There was a very interesting discussion on Linkedin started by Brian about a special sumproduct-like case, where Peter Bartholomew posted a very clever and beautiful mathematical solution. Here we would like to share the trick with you in a bit more generalized way.The challenge is to calculate the sum of the product of matrix elements by rows - using one formula in one cell only, without help columns.On the picture above we used a help column with PRODUCT formula. For the one-cell formula solutions we will separate two cases:1. if your data is in a sheet range2. if your data is an array of numbersIn case of range data, the solution is based on the special feature of SUBTOTAL and OFFSET formula described in an older post: Array of ranges using OFFSET formulaThe formula for the above case could be as simple as:`=SUM(SUBTOTAL(6,OFFSET(A1:C1,{0;1;2;3},)))`To be array-entered, the result will be 196.The limitation of this solution is that OFFSET could only be used with sheet ranges. So if you need to do any other change on your data, it could not be used.The idea to solve the array-case comes from secondary school mathematics when we learned the fact that logarithm of a product is the sum of the logarithms of the factors (http://en.wikipedia.org/wiki/Logarithm)E voilá, the formula:`=SUM(EXP(MMULT(LN(A1:C4),{1;1;1})))`Also to be array-entered.So we summarize the logarithms by rows (MMULT will do it) then use EXP to reverse the logarithm and SUM to produce the total result.With this formula you can use array of numbers:`=SUM(EXP(MMULT(LN({1,2,4;3,5,6;2,1,7;3,4,7}),{1;1;1})))`The real challengeSo you can do the data-mainpulation steps within this formula. Let's see how it was in the original question:There is only one vector with numbers between 0 and 1 in range a1:a10. The task was to calculate the sum of the following pattern:`=SUM(``(1-A1),``(1-A1)*(1-A2),``(1-A1)*(1-A2)*(1-A3),``(1-A1)*(1-A2)*(1-A3)*(1-A4),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8)*(1-A9),``(1-A1)*(1-A2)*(1-A3)*(1-A4)*(1-A5)*(1-A6)*(1-A7)*(1-A8)*(1-A9)*(1-A10)``)`To use the first, range-based solution, we need a help column: In column B we have to calculate 1-values, then the above formula could be used based on the numbers of column B:`=SUM(SUBTOTAL(6,OFFSET(B1,,,ROW(A1:A10)-MIN(ROW(A1:A10))+1)))`(array-enter)Using the logarithm-based formula, no help column is needed, the final solution could be written as:` ``=SUM(EXP(MMULT(1-(ROW(A1:A10)

#### Multiple lines across categories - simple step by step guide

posted Feb 12, 2014, 3:08 PM by roberto mensa   [ updated Feb 12, 2014, 3:09 PM ]

Medical Student: Well sir, I'm not sure I understand the distinction between reflexive and voluntary nerve impulses.
[...]

by The FrankensTeam

We started by reading the tutorial by Jon Peltier and the initial question on HelpmeViz. The basic problem is to add panel-lines representing a new data series to a column chart where the columns are grouped to 12-month series by different countries.
As you can see in Jon's tutorial, the original data table is not applicable for the combined column-line or line-line chart.
Jon presented how the tables could be done but had not shared a file ... so we downloaded the original, and have worked on it.
Below we will show you the whole process from the first step: how to the create the support tables (based on two different data table structures), then continue by showing how we created the charts: some (small and large) differences compared to the tutorial by Jon.
With just 10 simple steps we can create these beautiful graphics.

Here is our step-by-step "how we did it" guide.

You can see the first table structure below (as in the downloaded file: countries are in rows)
Define the below names on the data:

`rng_1 =\$C\$4:\$N\$12`
`rng_2 =\$C\$14:\$N\$22`
`states_1 =\$B\$4:\$B\$12`

You can find the pre-written formulas on sheet Support_1 - as you can see below:
(The first row contains error - it is normal, after copying the formula, you will see the structure of the data.)

Formulas are:
`A2 =IF(E2=INT(COLUMNS(rng_1)/2),INDEX(states_1,F2)," ")`
`B2 =INDEX((rng_1,rng_2),\$F2,\$E2,COLUMN(A1)) and drag in C2`
`D2 =0`
`E2 =MOD(ROW(A1)-2,COLUMNS(rng_1)+1)+1`
`F2 =INT((ROW(A1)-2)/(COLUMNS(rng_1)+1))+1`

note:
If you have 3 or more blocks of data ... You can drag the formula of B2 to more columns ...  then add the range in the first argument of the INDEX function.

### Drag formulas

Drag down A2:D2

Error values will appear from row 119:

So let's stop at line 119 including last line with error:

Now select the range: start from D119:

and with Ctrl+Shift+Left arrow and Up arrow to select A1:D119.

Insert an XY Scatter chart: lines and no markers:

Now you can see this:

ummm.... blood dipping? or nerve impulses? :-)

### Setting x axis and vertical gridline

Set the X axis boundaries from 1 to 9 ×13 + 1 = 118 and units to 13 so it has a tick label (and vertical gridline) in every 13th unit, .

`You can` `get 13` `by this formula`
`=COLUMNS(rng_1)+1`
`and 118 by this formula`
`=COUNTA(rng_1)+ROWS(rng_1)+1`

Delete x axis.

This is the result:

The series "label" serves as holder of labels for the groups, so as x axis labels.
Select series "label".
Set the color of the line to No line.
Add datalabels to the series with Below position.

The label should show the x values.

### Filter and clear errors value

Add Autofilter to the formulas and filter only the #REF! error values in column B:

Apply the filter, so you can see only the rows with #REF! error:

Delete all these formulas:

Remove the filter, so you can see empty rows in the table:

And the result for the chart: gaps appear between the country-groups:

### Final clean up

Delete Label series name from legend.
Zoom the chart and choose colors and styles you like. The final result can be something similar:

### Column and line chart

Select series "blu" and change the chart type to Column chart (in Italian: Istogramma)

The result is this:

Set the x axis options: write 13 to Interval between tick marks and also to Specify interval unit. Set the Position Axis to On tick marks.

Decrease the gap between the bars  to 0 and add a white border to the bars, so this can be the result:

### Step 1 and 2 for different data table structure

The second data structure that we consider (which I prefer) is a table with the states in the column header, you can then append new data blocks

Define the names of the ranges:

`rng_3 ``=Table_2!\$B\$5:\$J\$16`
`rng_``4 ``=Table_2!\$B\$17:\$J\$28`
`states_2 ``=Table_2!\$B\$4:\$J\$4`

`A2 =IF(F2=INT(ROWS(rng_3)/2),INDEX(states_2,1,E2)," ")`
`B2 =INDEX((rng_3,rng_4),\$F2,\$E2,COLUMN(A1)) and drag to C2`
`D2 =0`
`E2 =INT((ROW(A1)-2)/(ROWS(rng_3)+1))+1`
`F2 =MOD(ROW(A1)-2,ROWS(rng_3)+1)+1`

The formulas are very similar to the previous ones ... from this point you can repeat the steps already described above.

The first file contains 2 different tables, names and first row with formulas on support sheet. You can use it to practice with the creation of graphics.
The second file contains both charts too.

#### Customer segmentation - dynamic template chart

posted Feb 9, 2014, 2:23 PM by Krisztina Szabó   [ updated Feb 10, 2014, 1:33 PM by roberto mensa ]

 Freddy: I think you can find the way out by yourself, can't you?Inspector Kemp: Of course. Until we meet again, Baron.by The FrankensTeamThis post is just to share our template file created to visualize customer segmentation. The problem came up in a post on Chandoo's site written by Jeff. There was a great discussion about the different visualization ideas - worth to read the comments of the post too. Also, you can find some more additions in this post on Junk Charts by Kaiser Fung.So here is our version:The base idea is to highlight groups using the average revenue of the group. The light-colored boxes serve as if all the customers from the group would provide the same (=average of group) revenue. This way the area of the light-colored box gives the total revenue of the group. Unfortunately the area is not easily comparable, so we added the total revenue of the group next to it as a label. If you use equally sized groups, so for example all the groups represents 50 customers, you can compare the size of the squares.Some technical details:We used stacked column chart type with 0 gap between the columns. The box and group labels are added with the help of xy scatter series.Your input data should be on Support sheet column A. You can see a table here - this makes the calculations and the chart dynamic.The file is prepared to work with up to 10 groups. The base of the chart is stacked column type: for each group two data series are added; one for the original values and another for the average (the missing part only, it is colored lighter). On the Data_and_Settings sheet you can find a column for Separator points - here you can set at which customer the group should end. Then in the next column you can set a label for the group. (The green color marks the next input cells.)How to determine the number of customer for the groups? The easiest way is the visual analyse. First, set up only one group with the total number of customers, then take a look at the chart, and move your mouse above a data point where you can see a significant change in the trend of the revenues:So your first group should be set by adding 14 as separator point... end so on.If you are interested in this solution, you can download our file.

#### Matrix bubble chart with conditional formatting

posted Jan 16, 2014, 8:25 AM by Krisztina Szabó   [ updated Jan 16, 2014, 8:26 AM ]

 Freddy:Dressed up like a million dollar trouperTrying mighty hard to look like Gary CooperMonster:Super-Dooper[...]by The FrankensTeamCharlie asked us to create a special version of the matrix bubble chart where the bubbles are colored red, green or orange according to value limits:red if data <10green if data >=100the rest is orange.In a chart colors could be assigned to data series, so we will need separated series for the colors. First we will define 3 new names following the color rules:`arr_r=rng*(rng<10)``arr_g=rng*(rng>=100)``arr_o=rng-arr_g-arr_r`(The last letter of the name stands for the color.)Now we can add the new data series to the chart, - for example here is the red series:X and Y values are the same, the new names go to bubble size. (In case of the green series, it is enough to modify the already existing series with the new bubble size.)The new series must be assigned to primary axis and scale bubble size to 50, and certainly choose the colors according to your needs. Also needed to add labels to the orange series. You can see these steps in the original post.The result:We created a general version where you can easily change the limits and rules for the colors:Here are the names used in this version:`arr_r=rng*((rng<'1'!\$C\$3)+('1'!\$B\$3="<=")*(rng='1'!\$C\$3))``arr_g=rng*((rng>'1'!\$C\$5)+('1'!\$B\$5=">=")*(rng='1'!\$C\$5))``arr_o=rng-arr_g-arr_r`Download the file.

#### Formula challenge: SUMIF from more sheets

posted Jan 10, 2014, 8:29 AM by Krisztina Szabó   [ updated Jan 10, 2014, 8:36 AM ]

 Freddy: This is the moment. Well, dear, are you ready?Inga: Yes, Doctor.Freddy: Elevate me.Inga: Now, right here?Freddy: Yes, yes, raise the platform.Inga: Oh, the platform, oh that, yeah, yes.by The FrankensTeamWe had found a similar question in a forum, and started to think: could it be done using formulas? Well, if you are reading this post, then you know the answer is yes.Let’s say you have this kind of data on some sheets:And you have a list of sheet names - we created a name for it:MySheets={“Company1”;“Company2”;“Company5”}Can you write a formula to fill in this table on a separated Summary sheet?If you would like to think, you can download our file with sample data. We publish the solution a little bit lower, so do not scroll down, but download the example without solution...............SolutionFormula for cell B4:`=SUM(SUMIF(INDIRECT(MySheets&"!\$a\$4:\$a\$100"),\$A4,INDIRECT(MySheets&"!R4C"&COLUMN()&":R100C"&COLUMN(),0)))`to be array-entered and copy down and right.It is not difficult, is it? INDIRECT could result an array of references for both the range and the sum range. SUMIF is able to “pair” these ranges and calculate the sum based on the criteria, then give back the result in array: one number for each sheets. That is why we have to use SUM, so to add up the sheet-results.And one small thing could also be interesting: we used R1C1 reference style in the second INDIRECT, so the second argument is 0. This way it is easier to concatenate the reference from strings. (Please note, if you use non-english language Excel version, this formula may not work for you. You will have to replace the R and C letters with your local equivalents.)You can download the file with the solution.So we are finished.Or not?Well, you know our mind never stops thinking.Maybe it is possible to create an array of sheet names using formulas? Continue reading here.

#### Formula challenge: create a list of sheet names based on a prefix

posted Jan 10, 2014, 8:25 AM by Krisztina Szabó   [ updated Jan 12, 2014, 1:07 PM ]

 Freddy: When I give the word, throw the first switch.Igor: You've got it, master.Freddy: Get ready.Freddy: Get set.Freddy: Go.Freddy: Throw the second switch.Freddy: Throw the third switch.Igor: Not the third switch?by The FrankensTeamA formula which lists sheet names according to a rule could be useful for example to summarize data from particular sheets. But could it be done without VBA, using Excel formulas? We would like to give the possibility to the readers who are interested in formula challenges to think about this problem.The challenge:In the file sheets are named according to a rule: prefix and number, so for example:prefix = “Company”sheets:Company1Company2Company3Company5The numbering is not necessary to be continuous.The task is to create a formula which will produce the list of existing sheet names in array: {“Company1”;“Company2”;“Company3”;“Company5”}And it will automatically capture the newly added sheets too.So, if you would like to think… do not scroll down!...............Two formula solutionsSolution with ERROR.TYPEThe idea is to test the existence of the possible sheet names ( prefix&ROW(\$1:\$100) ) and acquire the numbers which results existing sheet. Then concatenate these numbers with the prefix will give the list we wants to have.`=prefix & SMALL( IF( ``ERROR.TYPE(INDIRECT(prefix&ROW(\$1:\$100)&"!A1"))=3`` , ``ROW(\$1:\$100)`` ), ``ROW( INDIRECT("1:"&SUM( --(ERROR.TYPE(INDIRECT(prefix&ROW(\$1:\$100)&"!A1"))=3)) ) )`` )`to be array-enteredHow does it work?The test is based on INDIRECT formula, which returns the reference specified by a text string. If the reference is invalid (so the sheet does not exists) the result is #REF! error. Using ERROR.TYPE we can check the result: it will give 3 if the reference exists and 4 if not. The test statement is nested to an IF formula, where you can see the array of the same numbers we concatenated to the prefix. The false argument of IF is omitted, so the result of the formula will contain numbers and FALSE values only, for example:{1;2;FALSE;FALSE;5;6;7;FALSE;FALSE;FALSE;FALSE;.....}The reason of leaving out the false argument (no comma separator is used!) is that we will use SMALL function in the next step, and in SMALL logical values are not counted.SMALL function will help to choose the numbers from this array. We need a correctly dimensioned array of numbers from 1 to the number of existing sheets - this is the second argument of SMALL.Solution using IFERROR and AREAS:`=prefix & LARGE( IFERROR( AREAS(INDIRECT(prefix&ROW(\$1:\$100)&"!A1"))*ROW(\$1:\$100) ,0), ROW(\$A\$1:INDEX(\$A:\$A,SUM(IFERROR(AREAS(INDIRECT(prefix&ROW(\$1:\$100)&"!A1")),0)))) )`to be array-enteredHow does it work?The base logic is the same, only the test part is different. We used a relatively rarely used formula: AREAS. The argument of the formula is a reference (or in our case: array of references, generated by the INDIRECT function). The result of the formula is the number of areas in the reference. Area is a range of continuous cells or a single cell. In our case, the references contain only one cell (A1) so the result will always be 1 or #REF! error if the sheet does not exist:{1;1;#REF!;#REF!;1;1;1;#REF!;#REF!;#REF!;#REF!;.....}After multiplying it by an array of the same numbers concatenated to the prefix, then put it into an IFERROR to replace the #REF!s with 0, we have this array:{1;2;0;0;5;6;7;0;0;0;0;.....}Now we can use LARGE to retrieve the non-0 numbers. The second argument of LARGE is built with the same logic as in the first solution for SMALL.Based on these solutions you can create your own formula for different rules, for example year numbers.Please remeber, adding a new worksheet does not trigger calculation, but renaming a sheet triggers it, so the formula will be re-calculated after renaming the sheet.ExampleMaybe you have already read the previous post about how to sumif from more sheets. This sheetname-list challenge originates from that post, so here we share a file contains the full model: sumif from more sheets with the possibility of adding new sheets with prefix-based names. Please download the file - you can find the first solution as name: MySheets.UDF solutionFinally, here is a user defined function with the possibility of excluding sheets from the sheet list. You can add the sheet names to be excluded as a range or a text array. If the argument is empty, the result will contain all the sheets of the workbook.`Function ListSheets(Optional vExcludeSheets As Variant)``    Application.Volatile``   ``    Dim asSheetNames() As String``    Dim vExcludedName, vWorkSheet As Variant``    Dim i As Long, bNeed As Boolean``   ``    For Each vWorkSheet In ThisWorkbook.Worksheets``        bNeed = True``        If Not IsMissing(vExcludeSheets) Then``            If IsArray(vExcludeSheets) Or TypeOf vExcludeSheets Is Range Then``                For Each vExcludedName In vExcludeSheets``                    If vExcludedName = vWorkSheet.Name Then``                        bNeed = False``                        Exit For``                    End If``                Next``            Else``                If vExcludeSheets = vWorkSheet.Name Then``                    bNeed = False``                End If``            End If``        End If``        If bNeed Then``            ReDim Preserve asSheetNames(i)``            asSheetNames(i) = vWorkSheet.Name``            i = i + 1``        End If``    Next``    ListSheets = Application.Transpose(asSheetNames)``End Function`Formulas: Igo-r, IngaKrisUDF: Igo-r

#### Our enormous ... 2013

posted Dec 30, 2013, 7:26 AM by roberto mensa   [ updated Dec 30, 2013, 7:35 AM ]

[the Monster starts to cry, and Freddy hugs him]
Freddy: This is a nice boy. This is a good boy. This is a mother's angel. And I want the world to know once and for all, and without any shame, that we love him. I'm going to teach you. I'm going to show you how to walk, how to speak, how to move, how to think. Together, you and I are going to make the greatest single contribution to science since the creation of fire.
Inga: [from outside] Dr. Fronkensteen! Are you all right!
Freddy: MY NAME IS FRANKENSTEIN!

[...]

by The FrankensTeam

Here on e90e50fx blog-site we publish our discoveries and share our special “push the boundaries” Excel-solutions. We write about only our own ideas, developments and discoveries, so proud to say what you find here could not be found elsewhere...

[...] with quiet dignity and grace.
[...] con calma dignità e classe.

At the end of the year we would like to take a moment to look back and summarize what we did in 2013.

### E90E50charts

The most important event of the year was the start of a new site: e90e50charts gallery where we collect our charts and data visualization tools. We are happy to see it has (until now) 123 unique, special Excel charts with downloadable files and links to the articles. We are always happy to read the visitor’s feedback in the Visitor’s Book.

### E90E50fx

On the e90e50fx site we published 41 articles. Unbeleivable... we did it all? We put together all this stuff? It's a lot... that's why we are running out of film quotes and will have to re-use some. :-)
This year the main topic was charts - and behind the charts many times you can find interesting and challenging formula solutions.
Here is the full list of our posts:

### E90E50

On e90e50 site we added the source code page of the calendar class and created a page for the feedbacks. We receive lot of thank you messages, questions and help-requests from the users. Calendar class is our most readed and most quoted post.

This year we published 3 new update of the calendar class.
Also on this site we added a new section to the “It-could-work” page based on a discovery about the connection between charts and calculation.
And last but not least, the team page was updated too. We grouped our posts by topics and added few more details about ourselves.

The end of a year opens a door to say thank you to all of our readers, followers, friends and colleagues who appreciated our work with their kind words. Thank you for your mails, comments on forums, thank you for the sharings, likes and +1s.
Stay tuned, follow us in 2014 too - it's sure there are still secrets waiting to be discovered!

#### Different color for up and down line segments in an Excel chart

posted Dec 23, 2013, 8:33 AM by Krisztina Szabó   [ updated Dec 23, 2013, 8:47 AM by roberto mensa ]

#### Error bar begin and end style setting differences

posted Dec 20, 2013, 7:58 AM by Krisztina Szabó   [ updated Dec 20, 2013, 8:02 AM ]

 Dr. Frederick Frankenstein: Sit down, won't you? [Igor sits on the floor] Dr. Frederick Frankenstein: No, no, up here. [Igor gets up onto a stool] by The FrankensTeamWhile we was working on the next post, we had realized that Microsoft changed the definition of begin and end points of error bars in Excel 2013. It could be important when you change the begin and end style of the error bar lines and would like to use the file in different Excel versions.In our file we set error bars on a very simple xy scatter chart.First we use fixed value error bars for both directions. On the below picture you can see how the begin arrows look like in the two different Excel versions:In Excel 2013 the begin point of the error bar is always the data point, irrespectively of the direction, so we can see difference only in case of negative error bars (below and left of the data point).To be sure how the new definition works, let's see what will happen if instead of fixed values, we use custom values (from sheet range). On the below picture we focus on the negative error bars with error bar values:The conclusion is that both for positive and negative custom values, the negative error bars are different in the two versions.The positive error bars look like the same for all values:

#### Visualize small change in large number - split the data

posted Dec 16, 2013, 7:28 AM by Krisztina Szabó   [ updated Dec 17, 2013, 4:01 AM ]

Dr. Frederick Frankenstein: For the experiment to be a success, all of the body parts must be enlarged.
Inga: In other vords: his veins, his feet, his hands, his organs vould all have to be increased in size.
Dr. Frederick Frankenstein: Exactly.
Inga: He vould have an enormous schwanzstucker.

by The FrankensTeam

We would like to show some tricks in Excel charts how to focus on a small change in a large population. The target is visualize the trend without overwhelming it by the total value.

#### The problem

How to visualize the growth of the proportion of women CEOs in Fortune 500 companies? This is a typical problem of large numbers: the population consists of 500 companies, while the number of women changes from 0 to 20. The change is significant, so there is a need to visualize it but also highlight that it is a only a small part of the total. The base idea is to split the total data to more rows to make the maximum bound of the chart axis lower - this way the important small values are clearly visible. The remainder range above the small value appears only in the last year.
Igo-r posted an example based on this idea in this discussion on Alberto Cairo’s site. Here is the solution made in Excel:

On the chart you can see the growth from year 1996 to 2013 but also have an impression about the total 500 if you take a look at the grey crowd.

#### Excel chart solutions

This chart could easily be done in Excel using a stacked bar chart type where the bars are filled with pictures.

#### Static version step by step

Let’s start the explanation with this simplified example:
The total data is 500: it will be splitted by 100, so we will have 5 rows and 100 elements in each at the bottom of the chart. This represents the total. The data of the last year is combined with the first 100 element to make the comparison easier.

Here is the data table and the series data used on the chart:

If you take a look at the numbers, you will understand how the red and grey series data was calculated. Using this data table you can easily create the chart:
• Select the red series and grey series range
• Insert a stacked bar chart
• Under the Select data menu of the chart set the Horizontal (category) axis labels: select the years range (no need to include the empty cells)
• According to your needs, format the vertical axis and set Categories in reverse order

Now you can see something similar:

The next step is to fill the lines with pictures. We used these two pictures:

(In Excel 2013 we realized that the chart displaying is extremely slow if you use large sized pictures, so we suggest to use relatively small ones. The above size worked well for us.)

• Select the lady-picture, and copy it (Ctrl+C)
• Select the appropriate data series on the chart (the blue)
• Go to the Format data series menu and choose the Picture or Texture fill option, then click on the Clipboard button and set the Stack and scale option to 1 as you can see on the below screenshot.

Do the same steps with the second series and the grey man picture. You will have to change the Gap Width of the series to a relatively low number to have wider bars and better-looking pictures.
(You can observe that using this fill technique, Excel is able to illustrate decimals by cutting the last small picture within the bar.)