Monthly Archives: February 2019

Functions

Search function

Product code consists of five letters. A report should count sales for any product codes which include at least one H letter.

Random product codes I have created with following function:

=CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85))

And sales :

=RANDBETWEEN(1;100)

If any of five letters is H, then the report should count the sales for that product.

I have solved this issue with SEARCH-function.

Search function returns value if the text to be found was found.

To differentiate the two results, you can use ISERROR function. The function returns value based on whether the cell contains an error.

If SEARCH function finds a value, the result is not an error. If SEARCH does not find the value, then the result is an error.

Now we can build the formula to solve the issue.

=IF(ISERROR(SEARCH(“H”;C5));””;”H here”)

The letter H is search in the cell C5. If the result is an error, there is not H in cell C5, then the result is blank. If SEARCH finds an H in C5, then the result is not an error, and IF returns the value H here.

The records, where H exist, are marked with H here text. Now we should count the sum for H here records.

=SUMIF(E5:E28;”H here”;D5:D28)

E-column holds the IF function to check H letters, D-column is for the sales volumes.

Excel

Last name, first name -> first name last name

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.

Excel

Functions for subgroups

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.