This is an extension to my previous blog. The formula in this example is taken from my previous blog.

Presuming you want to analyze how a change in input values changes the result of the formula. Like when beta ranges from 0,6 to 1,6 and return for market portfolio from 2,5 to 6,5. What are the expected values for the capital asset.

Make a table as presented above. Formula in B6 is shown in B5. We select return for market portfolio and beta as variables. The risk-free rate is static 0,5 %.

Formula in E3 is written in E2.

Activate the data area and select data – what-if analysis – data table

Row is beta and column return for market portfolio. Press ok.

The result.

If the return for market portfolio is 4 % and beta is 0,8 and the risk free rate is 0,5 % then the formula returns the value of 3,3 %.

We can check this.

With custom formula.

Manually calculated.

In case Excel is showing some weird errors like Excel cannot save the workbook at all, it is worthy to check that all the updates are implemented correctly.

This is not a waterproofed solution but rather a hint how you can start with this issue.

In Excel select file – account.

Check updates with update now button.

Office (and Excel) was up to date.

To check Windows 10 updates, press square in lower left hand corner and select settings.

Then choose Update & Security.

Press Check for updates.

All updates are in.

If any updates are installed, restart the computer even though it was not required.

If Excel for some reason cannot save document or something is corrupted, updates do not necessarily solve the issue. It is the first thing every user can do to have latest updates both for Windows and Excel.

A manual journal entry is made. System does not allow the journal entry to be posted as debit and credit have different balances.

Debit postings are entered with three decimals. Credit balances are equal to debits, but rounded to two decimals. The value in B3 is not 4,44 but 4,444, even though B3 looks like 4,44. The value in C3 is 4,44. The ERP takes three decimals, even though showing only two, as Excel is doing also.

Formula texts are presented for the cells C3:C6.

When journal entry is made both debit and credit balances should be handled in the same way. It is not material difference whether the sum is 8,40 or 8,39. Same policy should be applied in all the manual journal entries for both debit and credit.

The sum of cells should be presented in brackets. Sum of 1, 2 and 8 equals to (11).

I have found two ways to tackle this issue.

First one is simply to treat the sum as a key string. The first digit is the first bracket, then the SUM function and finally the second bracket.

The formula in C3 is also here for copy pasting.

=”(” & (SUM(B4:B6)) & “)”

Another option is to change the cell format. Write the normal SUM function to C4 saying = SUM(B4:B6) as usually. Then select home – number of activate cell C3 and press control+1.

Select Custom in write (0) in Type: field.

For further calculations with sums in brackets:

The key string with brackets is considered to be negative.

In the second option, with format cells, Excel understands the C3 as a positive value.

Vlookup is simple.

You have to define the lookup value. For the first case in the line number 6, the lookup value is 2. The table, where the value is looked up is A2:B4. Column index is 2, as the values are looked up one row right from the lookup value. False means that we are looking for exact value 2 in row A not a value closest to 2. Then the VLOOKUP returns the value qw.

If VLOOKUP does not find the value 3, it returns the value #N/A. Some other value might cosmetically be better like “not found” or just blank cell.

We can use ISERROR function. The formula tells us whether the function returns a reasonable value or not.

IF the VLOOKUP returns a value with error, then print “not found” or blank. IF the VLOOKUP returns a value without error, then use the normal VLOOKUP function.

To copy paste the IF sentence:

=IF(ISERROR(VLOOKUP(A6;A2:B4;2;FALSE));”Not found”;VLOOKUP(A6;A2:B4;2;FALSE))

If a blank cell is better than any text, then just delete Not found text and use the quotation marks like “”.

Date formats in Excel have caused issues for me. For example the format used in US month.day.year should be converted to day.month.year format. Eg. 2.12.2019 should be 12.2.2019.

You can always take home-number | format cells – number -date selection. However, sometimes format cells functionality has not helped me.

One option is to consider date as a normal keystring.

There are four options.

1. Second and fourth digits are dots like 3.4.2019. In this case IF-function should take the third digit then add dot, then the first digit from left and add dot then take four digits from  the right.
2. Third and fifth digits are dots like 12.2.2019. IF sentence picks up the fourth digit the adds the dot. Then takes two digits from left, adds the dot and takes four digits from the right.
3. Second and fifth digits are dots like 3.20.2019. IF statement selects the third and fourth digits then adds a dot. Digit from left is selected, then a dot is added. Four digits from right is chosen.
4. Third and sixth digits are dots like 11.25.2019. IF command takes fourth and fifth digits and then adds a dot. Two digits from select are picked and then a dot is placed. Finally, as earlier, four digits from right are selected.

=IF(AND(FIND(“.”;A4)=2;(FIND(“.”;A4;FIND(“.”;A4)+1))=4);(MID(A4;3;1)&”.”&(LEFT(A4;1))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=3;(FIND(“.”;A4;FIND(“.”;A4)+1))=5);(MID(A4;4;1)&”.”&(LEFT(A4;2))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=2;(FIND(“.”;A4;FIND(“.”;A4)+1))=5);(MID(A4;3;2)&”.”&(LEFT(A4;1))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=3;(FIND(“.”;A4;FIND(“.”;A4)+1))=6);(MID(A4;4;2)&”.”&(LEFT(A4;2))&”.”&(RIGHT(A4;4)))))))

In case you have similar case, but you have slash / instead of dot. Just copy the formula line above and paste that to notepad. Then press control + H to replace all the dots with slashes.

When you implement this IF sentence to your Excel, you can change the target cell from A4 to the cell address where you have the data, it useful to change the cell with note pad as explained above.

IF sentence is bit long to be presented in screen shots, but you can test by yourself. Like writing value 1.23.2019 to A4 cell. Then paste the IF command above the next cell. See what happens.

This solution is somewhat complex, hopefully some easier solutions could be found too. I needed to tackle some different date formats, and then I created the IF sentence above.

The names were in format last name, first name. The names should be first name last name.

Lastname, Firstname should be Firstname Lastname.

AAlastname, BBBfirstname should be BBBfirstname AAlastname.

The formula chain will be build with MID, FIND, LEN and LEFT formulas.

MID selects digits in a string MID(cell where the text is; start point in the string; number of digits to be selected).

FIND defines the order number of a defined digit in a string FIND(what to find; where to find; which character to start with (optional)).

LEN returns number of digits in a key string.

LEFT selects a number of digits starting from left in a keystring.

Back to the original issue.

The formula in C2 is:

=(MID(b2;((FIND(“,”;b2)+2));((LEN(b2)-(FIND(“,”;b2)))))&” “&(LEFT(b2;(FIND(“,”;b2)-1))))

First MID function is starting two digits left from comma, there should be the first digit of the last name. Last digit of first name is the last digit of keystring, that can be found out with LEN.

After a space, the last name is selected. The first digit of the last name is the first digit of the keystring. The last digit of the last name is the digit two steps left to the comma.

Please note, if you copy the Excel sentence above and paste it to your Excel, and the sentence does not work. Then update the quotation marks in eg. notepad. Somehow, when I test the sentence in Excel and paste it to blog, the font is different. After copying the sentence from blog and pasting it to Excel, Excel does not understand anymore the quotation marks, as the font change changed also the quotation marks.

Sales order data is reported. Only dimensions in the report are product and sales volume. Products sold are A, B, C or D.

Functions like sum, average, max and min should be calculated per product. Eg. How many pieces of product A was sold, how much was the highest single sales volume for product B, how much was the average sales volume for product C and so on.

For generating data for products A, B, C or D, we need CHAR function. Function CHAR returns a digit eg. CHAR(65) is A (upper case a), and CHAR(97) a (lower case a).

=CHAR(RANDBETWEEN(65;68))

Excel randomly selects 65, 66, 67 or 68. That means A, B, C or D.

For sales volumes any value between 1 and 10.

=RANDBETWEEN(1;10)

That is the sales volume data.

You can create sales data manually but using RANDBETWEEN function reduces manual work.

Sum for product can be counted with SUMIF and SUM functions.

For SUMIF function, you need to define where product range is found, in our case cells are C4:C27. As the idea is to copy the formula, make this reference as absolute reference. Next you need to define product, which is search in product range, that is in G9. The product should change when the formula is copied further, therefore this reference is relative reference. Last, the sum range needs defining, the sum range can be found in cells D4:C27 where is the sales data. The reference should be absolute because even though the formula is copied, the sum range is the same.

With SUMIFS function you can count with several criterias. In this example only one criteria, product, is used. It anyway returns the same value as SUMIF.

Using SUM function is somewhat more complex, this is my opinion. The formula you need to write to the cell H9 is:

=SUM(IF($C$4:$C$27=G9;$D$4:$D$27)) and press SHIFT + CONTROL + ENTER the result is:

{=SUM(IF($C$4:$C$27=G9;$D$4:$D$27))}

SUM function needs nested IF formula, inside IF, you need define product range C4:C27 as absolute reference equals to wanted product value in G9. Last you need to define sum column meaning the sales data. That is in cells D4:D27. Mark sales data with absolute reference as it should not be changed when the formula is copied downwards.

AVERAGE formula works in the same way as SUM function. You just replace SUM by average like

=AVERAGE(IF($C$4:$C$27=G9;$D$4:$D$27))

Don’t forget SHIFT + CONTROL + ENTER.

You can change AVERAGE to some other formula too. If you want to know standard deviation per product sales data, you can use STDEV.S or STDEV.P instead of AVERAGE.

Finnish postal code consists of five number. The first number or first two numbers might be zero. Postal code for Lauttasaari is 00210.

First option is to apostrophe at the start like ‘00210.

In Excel it looks like this.

Second option is to modify format cell.

Select control + 1 or select home – number – arrow lower right hand corner to receive the format cell -window.

In the window select custom category and enter 00000 (five zeroes) to type: box.

Third option is to use TEXT formula.

For the TEXT formula you need to define the cell, where the text is, and format_text 00000 (five zeroes).

Personally, I like the third option as then you can copy the formula downwards, in case you have a list of postal codes.

When you make accounting postings for an account and after the period, you want to check the balance of an account. You need to count both debit and credit side. If sum for debit side postings is higher than sum of credit, then you need to add the difference to credit side to match both debit and credit sides. In this case the account has debit balance.

The formulas presented are in cells F16 and G16. If you want to copy paste the if sentences, they are here:

=IF(SUM(G4:G14)>SUM(F4:F14);SUM(G4:G14)-SUM(F4:F14);””)

=IF(SUM(F4:F14)>SUM(G4:G14);SUM(F4:F14)-SUM(G4:G14);””)

If formula in debit side checks first if sum for credit postings is higher than sum of debit postings, in this case, the difference between credit and debit is counted. If the if sentence is not true, sum of debit postings is higher than credit postings, then the cell should be blank, as in that case we have credit balance.