Excel Tips and Tricks

Create year , quarter , month, Week, day, hour

2021-05-09T04:16:00.001-07:00

We often need to do summary reports based on week, month, quarter, year and so on.

Let's see how to extract these info from time stamp.

For example lets consider 1/1/2021 4:00:00 AM

Hour

=TEXT(A2,"hh")

Day

=TEXT(A2,"dd")

=TEXT(A2,"ddd")

=TEXT(A2,"dddd")

Week

=WEEKNUM(A2)

Month

=TEXT(A2,"mm")

=TEXT(A2,"mmm")

=TEXT(A2,"mmmm")

Quarter

To calculate quarter we will use month info.

=ROUNDUP((MONTH(A2))/3,0)

Some companies use different starting month for quarter need to adjust the month.

Year

=TEXT(A2,"yyyy")

Preparing Data for Stacked Column Chart in Excel

2015-09-06T05:16:00.001-07:00

I recently had a situation where I have part measured repeatedly after rework. The number of times the each parts measured and which measurement belongs to which rework is only can be identified by "time stamp" (the time of the measurement). Please refer to the sample data. Please note the data is sort by time stamp and part name.

Now I need to a Stacked Column Chart like below. This is to have visual representation of the effect of rework. My original intention to use Qliksense or Tableau but excel can do the job with an additional table.

Step 1: First sort the table by part and then by time stamp.

Step 2: Add a column (column D) with the formula

="Measurement No" & COUNTIF(B$2:B2,B2)

Step 3: Drag the formula all the way.

Step 4: Now you can create the table for the Stacked Column Chart.

The table should be like the image below.

Add formula =SUMIFS($C:$C,$B:$B,J$1,$D:$D,$I2) in cell J2.

Fill the table with the formula.

Use insert → chart → stacked column to create the chart.

How to use "INDIRECT" function

2015-03-14T02:46:00.000-07:00

Lets say we have production log sheet for technicians to put a "x" in the relevant cell, as shown below. Technicians assigned working on the particular date enters a cross "x" in the corresponding row and corresponding column (his/her name).

Then we need to get all the names in column C. This will be very help full in many ways.

Use formula in cell C2 and populate as needed.

=INDIRECT(ADDRESS(2,MATCH("x",D3:K3,0)+COLUMN()))

Lets analyse the formula,

1. INDIRECT needs the cell reference.

2. To get the reference cell I am using ADDRESS function. Address function requires row number and column number.

3. Row number is 2, as that is the contains name of the technicians.

4. Column number is bit complicated. Use MATCH function to find the column number within the selected range (in this example D3:K3). Use COLUMN function to find the column number where the result to be displayed (column C in this example).

MATCH("x",D3:K3,0)+COLUMN()

The result will be like image below,

Comments are welcome.

How to use AVERAGEIF and AVERAGEIFS

2015-03-01T08:25:00.002-08:00

In the earlier posts we have looked at using SUMIF, SUMIFS, COUTIF AND COUNTIFS. In this post lets see how to use AVERAGEIF and AVERAGEIFS,

Lets look at the following example:

Let say I need to calculate the average quantity produced for each day.

First make a list of dates (unique).

=AVERAGEIF(E:E,A3,G:G)

Extend the formula as needed.

Let say i need the average by machine (1 to 9) and date then i need to use AVERAGEIFS.

The results will be,

The formula is

=AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2)

Please note the quantity is in column U, dates in column S and machine number in column T.

I have added IFERROR function to get rid of #DIV/0! error

=IFERROR(AVERAGEIFS($U:$U,$S:$S,$A3,$T:$T,B$2),"")

Please leave your comments, thanks.

How to use formulas with Chart titles

2014-01-30T19:29:00.002-08:00

This is a very simple trick but seems many not aware of it. Let say we need a chart for daily production out put and the data is updated every day.

Lets add (insert) a bar chart.

Select the data

Insert chart

The chart looks like,

Now need to add some titles for horizontal and vertical axis.

Select "DESIGN" from menu → select "Add Chart Element" → select "Axis Titles" → select "Primary Horizontal"

Do the above for "Primary Vertical" also.

The chart will look like,

Now change the "Axis Title".

Click on the Horizontal "Axis Title" and in the "Formula Bar" type =Sheet1!$B$5.

For vertical axis title type =Sheet1!$C$5.

Now the chart looks like,

Now, Chart title need to be changed to "Output report for 25 January 2014" and if the data changed to another date the chart title to be changed accordingly. This is where we need a bit more complicated formula.

In a cell (E1) key in the following formula

=CONCATENATE("Output Report for ",(CONCATENATE(DAY($C$3)," ",TEXT($C$3,"mmmm")," ",YEAR($C$3))))

(CONCATENATE(DAY($C$3)," ",TEXT($C$3,"mmmm")," ",YEAR($C$3)))) = 25 January 2014

Please pay attention to the month "TEXT($C$3,"mmmm")" formula.

Then click on the chat title and key in =Sheet1!$E$1 in the formula bar.

Now the chart will look like,

Now if the data changed to 1 March 2014, the chart will be changed automatically,

Please leave your comments, thanks.

Arranging names in alphabetical order and placing them under the alphabet

2013-12-18T05:39:00.000-08:00

Lets say we have 50 names and the names to be put under them starting alphabet in alphabetical order.

Below is the sample of names.

Sample of expected results, names under their respective first letter.

OK, lets see how to do this.

In this example names are in worksheet 1.
I am going use another sheet (sheet 2) for the intermediate sorting and worksheet 3 for the final results.

In the intermediate sorting, I will list the names under each alphabets, but not in sorted.

In sheet 2 lets key in Alphabets (A, B, C, ....) in alternate columns as shown below. Make sure to have a empty column before the column with alphabet A.

In C2 key in =IF(LEFT(Sheet1!$D2,1)=Sheet2!C$1,Sheet1!$D2,"ZZZZZ") H

Lets take a look at the formula and analyse what it does.

The condition for the IF function is LEFT(Sheet1!$D2,1)=Sheet2!C$1. I am just taking the first letter of the name and comparing against the Alphabet, in this case "A".

The result if "TRUE" is the name (in the corresponding row) in Sheet 1. The result if "FALSE" is "ZZZZZ". Why "ZZZZZ"? Let me explain later.

Note: How to use LEFT command please refer to this post. http://newexceltips.blogspot.sg/2012/06/how-to-use-date-right-and-left-formulas.html

Drag the formula as needed. The result is like shown below.

As you can see the names appear under their respective first alphabet. But there are lots of "ZZZZZ" and the names are not sorted A-Z.

Now I make use of the empty column in-front of every column with names. For example for column C which contains names starting with" A" I use cell B2 to key in the following formula.

=COUNTIF(Sheet2!C$2:C$51,"<="&Sheet2!C2)

This formula will give numbers corresponding to the name in the list. For example if we have name AA, AZ and AD, the corresponding numbers will be 1, 3 and 2. This is exactly what a alphabetical sorting order should be. So "ZZZZZ" will be given max value, in this example 50.

In another sheet you can have Alphabets on one row and use VLOOKUP to pull the names in the sorted order.

The Vlookup command is (in cell B2)

=VLOOKUP(ROW(B2)-ROW(B$1),Sheet2!B:C,2,FALSE)

Notice the error in cell B6 onwards after dragging the formula.
To correct this add a IFERROR formula to VLOOKUP formula.

=IFERROR(VLOOKUP(ROW(B2)-ROW(B$1),Sheet2!B:C,2,FALSE),"")

Now extend the formula to all the alphabets (A-Z) to complete the table.

If any addition of names in the original list will be updated automatically (the formula range should cover the cell). For example add "Aann".

Please leave your comments.

How to use INDEX function - part one

2013-01-11T20:20:00.000-08:00

We have seen how to use VLOOKUP in the earlier posts,

http://newexceltips.blogspot.sg/2012/07/how-to-use-column-and-columns-functions.html and

http://newexceltips.blogspot.sg/2012/05/how-to-use-concatenate-to-create-tag-or.html.

In the same way we can use HLOOkUP (vertical and horizontal).

But if we need to use vertical and horizontal look up at the same time, we need to use INDEX.
Lets say we have table like below,

and say we need to make a table like below,

In the cell J3, type =INDEX( and choose

As you can see we need an array (table), row number and column number. To find corresponding row number and column number we need to use Formula "MATCH".

for example

Row number MATCH(H3,A3:A11,0)
Column number MATCH(I3,B2:E2,0)

Lets look how the formula looks like,

Note the row number and column number needed are of the array (table) not of the worksheet.

So here is the formula,
=INDEX(B3:E11,MATCH(H3,A3:A11,0),MATCH(I3,B2:E2,0))

As usual we need to lock the cells.
=INDEX($B$3:$E$11,MATCH($H3,$A$3:$A$11,0),MATCH($I3,$B$2:$E$2,0))

Then drag the the formula to the cells as needed.

We can also use as array formula.
=INDEX($B$3:$E$11,MATCH(H3:H5,$A$3:$A$11,0),MATCH(I3:J5,$B$2:$E$2,0))
Remember to use Shift+Ctrl+Enter

If we are expecting wrong Machine or Data (accidental or otherwise) then we can add a

IFERROR (Excel 2007 onwards).
=IFERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)),"NO VALUE")

or ISERROR (Old versions of Excel)
=IF(ISERROR(INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))=TRUE,"NO VALUE",INDEX($B$3:$E$11,MATCH($H6,$A$3:$A$11,0),MATCH($I6,$B$2:$E$2,0)))

That's it. Your comments are always welcome.

How to split a cell diagonally

2012-12-22T08:56:00.000-08:00

Let say we need to split the cells diagonally. Mainly when we want make a nice looking table.

Let say we need a table showing machine numbers and outputs per day.

As you can see event though the data can be represented properly, the presentation part is not very good as there will be empty cells (colored in yellow).

A nicer table should have "Date" and "Machine" in the same cell. Just like the one below,

Let see how to do this.

1. Right click on the cell where we want to put "Date" and "Machine" and click on Format cells.

Select "Border" and click on the diagonal border.

Click "OK"

Set the cell's Horizontal Alignment to "left" and vertical alignment to "center".

Type in "Date" then Hit "Alt+Enter". The type in "Machine".

Use the space bar to push the "Date" to right to get what we want.

If you want to have two colors for the split area you can try the "Fill effects". Choose "two colors" and "Diagonal Down". This is the best I can think of.

Please note if you want to use the value (date machine), this is not the method.

As always love to have your comments.

How to use #N/A to create line plots look more presentable

2012-10-06T21:59:00.000-07:00

Let's look at the example below. The table showing rejects (column B), output (column C) and reject percentage (column D). Reject percentage = B2/C2.

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#DIV/0!
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Note on the fourth day there was no production. So we get an error in the reject percentage column. If we plot the reject percentage, We will get,

On day four there is no production and so no rejects.
The plot is not accurate as it shows reject percentage as zero.

Ideally the plot should ignore day 4. To achieve this we need to change the formula in column D.

=IF(ISERROR(B2/C2)=TRUE,#N/A,B2/C2)

Now the table looks like,

Day	Rejects	Output	Reject Parentage
1	5	300	1.67%
2	80	400	20.00%
3	30	350	8.57%
4	0	0	#N/A
5	45	455	9.89%
6	67	340	19.71%
7	43	360	11.94%
8	89	400	22.25%
9	23	320	7.19%
10	56	330	16.97%

Now plot the reject percentage again.

Now the plot ignores day 4 and it shows no data point on day 4.

As always love to have your comments.

How to find the position of last of the multiple characters in a cell

2012-09-17T06:46:00.000-07:00

Let's look at the example here. There are cells that contain something similar to

drive1\folder1\file1023
drive2\folder2\subfolder2\file256
drive10\file34
..
..
..

The text after the last "/" is to be extracted, the result should be,

file1023
file256
file34
..
..

To do that you need position of the last "/". FIND function alone is not use because of two unknowns, one how many "/" s are there and second what is the "start number" to use.

But it is possible using RIGHT, FIND, SUBSTITUTE and LEN.
Let's say drive1\folder1\file1023 is in cell A1.

The formula to get file1023 will be

RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

Note '#' can be any character.

Let's analyse the formula to see how it works. The formula works using [instance_num] (Which is not available in FIND function) of the SUBSTITUTE function and substituting "\" with "" (or nothing).

SUBSTITUTE(text, old_text, new_text, [instance_num])

Lets look at the formula from right to left.

LEN(SUBSTITUTE(A1,"\","")) will give number of characters after replacing "\" with "".

LEN(A1) will give number of characters before any change.

The difference is the number of "\" in the cell, in this example 2.

SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) will replace only the last "\" with "#" using 2 as the [instance_num ]. Now "#" is in the last "\" position.

Now using FIND the position of "#" can be found, in this example 15.

Using RIGHT formula with identified position of the "#" it is possible to extract file 1023.

LEN(SUBSTITUTE(A1,"\",""))	21
LEN(A1)	23
LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))	2
SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))	drive1\folder1#file1023
FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))	15

RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))	file1023

Please let me know if you have any concerns or questions.

How to find duplicates in column without deleting them

2012-09-02T09:56:00.001-07:00

It is a common requirement to find duplicates, in most cases the duplicates need to be deleted, but in some cases the duplicates need to be kept.

Excel 2010 (even 2007?) got the feature under "DATA" to remove duplicate entries in column(s), called Remove Duplicates.

The issue with this tool it removes the duplicates and do not give more details.

If you want to mark the duplicates or want to VLOOKUP unique values you need use COUNTIF.

Let's see the example below,

There are many duplicate entries i column D. If you want to put "Duplicate" in column C, use

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

The result will be,

Important to note, if there is two or more values, all considered "DUPLICATE". This will not help if you are looking to use unique values. For example if you try to index the unique values, let's say in column B.

You may want to use,

=IF(C2="DUPLICATE","",MAX(B$1:B1)+1)

The result will be a "1" in B15. In other words there is only one unique value in column D. But we know it is not true. To solve this we need to change the COUNTIF slightly.

Instead of

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

We will use,

=IF(COUNTIF(D$2:D2,D2)>1,"DUPLICATE","")

and the result is,

Now you can use VLOOKUP to pull the unique values, Vlookup(1,B:D,2,FALSE) and so on.

The Key Point is to leave the first instance of the duplicates as "Unique".

There will be lot of other ways to do the same.
I will be happy to have any feedback.

How to use COLUMN and COLUMNS functions.

2012-07-17T07:16:00.000-07:00

Lets look at the two functions which sound similar but give different results. COLUMN gives the position of the column and COLUMNS counts the number of columns in a range.

Let's see the syntax.

COLUMN([reference])

COLUMNS(array)

Examples
COLUMN(F2) = 6
COLUMN() = will give the column position of the cell where the function is.

COLUMNS(F10:I14) = 4

One use of the COLUMN function is with VLOOKUP.
Consider the example below, A table of outputs of different machines in different days.

Now I want to pull only machine 3 data to another place using VLOOKUP. First I key in Machine 3 in A6 for this example.
I need the values in column I to L to appear in column B to E.

I can key in =VLOOKUP($A6,$H:$L,2,FALSE) in B6, =VLOOKUP($A6,$H:$L,3,FALSE) in C5 and so on.
But I need to keep changing the col_index_num for every column. If you are working with a large table it can be tedious work.

Here we can use column function to make it automatic when we drag the function. The formula looks like this,

in B6 =VLOOKUP($A6,$H:$L,COLUMN(I:I)-7,FALSE)

COLUMN(I:I)-7 = 2

When you drag the function,
in C6 =VLOOKUP($A6,$H:$L,COLUMN(J:J)-7,FALSE)
in D6 =VLOOKUP($A6,$H:$L,COLUMN(K:K)-7,FALSE)
in E6 =VLOOKUP($A6,$H:$L,COLUMN(L:L)-7,FALSE)

You can use COLUMNS(A:G) instead of 7
=VLOOKUP($A6,$H:$L,COLUMN(I:I)-COLUMNS(A:G),FALSE)

Depending on the table location and where you want to pull the data the function for col_index_num will change.

Please let me know comments/feedback.

Excel Tips - How to use CHOOSE function

2012-07-14T18:57:00.002-07:00

Another simple function which seems nothing much of a use at the first look. But it can be used choose from a predetermined list.

Syntax

CHOOSE(index_num, value1, [value2], ...)

index_num and value1 and necessary. value2 to value255 (excel 2010) is optional. If index_num is 1 then value1 is the result, if index_num is 2 then value2 is the result and so on.

For example, let's say we need to know the "DAY" of a certain date. To do that we need to use another excel function WEEKDAY.

The week is from Sunday to Monday,

DAY	WEEKDAY
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7

So, CHOOSE(1,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") will give Sunday. It is possible to have the days in cells. But for this example I will keep within the function.

Now, instead of index_num = 1, we will use =WEEKDAY(4th July 2012). Cell A14 contain the date.

=CHOOSE(WEEKDAY(A14),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Just to illustrate I made a table.

4 July, 1999	Sunday
4 July, 2000	Tuesday
4 July, 2001	Wednesday
4 July, 2002	Thursday
4 July, 2003	Friday
4 July, 2004	Sunday
4 July, 2005	Monday
4 July, 2006	Tuesday
4 July, 2007	Wednesday
4 July, 2008	Friday
4 July, 2009	Saturday
4 July, 2010	Sunday
4 July, 2011	Monday
4 July, 2012	Wednesday

CHOOSE can be used in conjunction with other excel commands like SUM.

As always, love to have your comments.

How to use AREAS function

2012-07-13T19:18:00.001-07:00

Let's look at the syntax.

AREAS(reference)

Well, not a complicated function. An area is a range of contiguous cells or a single cell. For example AREAS(B1) = 1 and AREAS(B1:B10) =1. Please note whether the cell contains a value or not will not change the outcome of the results.

If we use multiple reference then the result will count of the references, for example AREAS((B2,B3,B11)) = 3.

There is another use of this function, which actually uses the " contiguous" part.

There are two lists, list1 and list2 in the worksheet. In this case AREAS will give the number of overlapping cells.

AREAS(list1 list2) = 6

As always love to have your comments.

Using ADDRESS function

2012-07-08T08:14:00.000-07:00

Lets look at the syntax first.

Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

1. row_num can be specified by numerical or by row() function.

2. column_num can be specified by numerical or by column() function.

3. abs_num has 4 choices,

abs_num	Returns this type of reference
1 or omitted	Absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)
2	Absolute row; relative column
3	Relative row; absolute column
4	Relative

4. a1 has two options, TRUE, 1 or omitted and FALSE or 0. TRUE, 1 or omitted will give A1 style and FALSE will give R1C1 style.

5. sheet_text specifies the sheet name in which the cell is located. Please note sheet names are case sensitive.

Lets look at the different possibilities.

Formula	Results
=ADDRESS(1,1)	$A$1
=ADDRESS(1,1,1)	$A$1
=ADDRESS(1,1,1,1)	$A$1
=ADDRESS(1,1,1,0)	R1C1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(ROW(A1),COLUMN(A1),1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,2,1,"Sheet1")	Sheet1!A$1
=ADDRESS(1,1,3,1,"Sheet1")	Sheet1!$A1
=ADDRESS(1,1,4,1,"Sheet1")	Sheet1!A1

Simply put this function will give the cell number. So whats the big deal about a cell number. Well it can be used in labeling.

Example

Look at the example below,

It works fine till a row or column added or removed.

Now the label become meaningless as name should be entered in E3.

The solution is to use ADDRESS function.

="Please enter the name in cell "&ADDRESS(ROW(E2),COLUMN(E2),4,)

It works fine even a row or column added or removed. By this way the label auto adjusts.

Add a row...

Add a column...

The label adjusts automatically.

In the same way a range can be specified in a label. Using formula as shown below,

="Please enter the name in cell "&ADDRESS(ROW(F3),COLUMN(F3),4,)&":"&ADDRESS(ROW(F20),COLUMN(F20),4,)

Love to have your comments.

Using ABS function

2012-07-07T21:20:00.000-07:00

ABS will give absolute value of the number or formula.

Let say there are three values -5,-10 and - 8 in A1, B1 and C1 respectively.

=ABS(A1) will give 5
=ABS(A1*B1*C1) will give 400

Using SUMPRODUCT to count

2012-07-04T07:47:00.000-07:00

Let's look at the example below,

In column C dates and column D the error happened on the day with possible multiple entries.

The requirement is to calculate the number of times error5 occurred after 5/5/2012 and before 5/15/2012. (answer is 5).

Lets put the two dates in A1 and A2. The formula will be

=SUMPRODUCT((C1:C22>A1)*(C1:C22<A2)*(D1:D22="error5"))

If you do not want enter dates in cells the formula will be

=SUMPRODUCT(($C$1:$C$22>DATE(2012,5,5))*($C$1:$C$22<DATE(2012,5,15))*($D$1:$D$22="error5"))

Love to have your comments....

SUMIF, SUMIFS and SUMPRODUCT

2012-06-30T21:11:00.000-07:00

Let's look at the example below.

Zone	Shift	No of Machines	Output per machine
Zone 1	Shift A	15	15
Zone 2	Shift A	15	15
Zone 1	Shift B	15	20
Zone 2	Shift B	15	20
Zone 3	Shift A	15	20
Zone 1	Shift B	20	20
Zone 3	Shift B	20	15
Zone 3	Shift B	20	20
Zone 1	Shift A	20	20
Zone 2	Shift B	15	20

The requirement is to calculate the output for Zone 1 for Shift B.

SUMIF

1. Using SUMIF would require two additional columns to be created.

2. One to CONCATENATE to Zone and Machine =CONCATENATE(I2,J2) and another to multiply column K and L =K2*L2

3. Then we can use ==SUMIF(M2:M11,"Zone 1Shift B",N2:N11)

SUMIFS

1. Using SUMIFS will require one additional column to be created.

2. Add a Column to multiply column K and L =K2*L2 (column M lo longer required).

3. Then we can use =SUMIFS(N2:N11,I2:I11,"Zone 1",J2:J11,"Shift B")

SUMPRODUCT

1. Using SUMPRODUCT will NOT require any more additional columns.

2. SUMPRODUCT is meant for "Multiplies corresponding components in the given arrays, and returns the sum of those products."

3. IF we use =SUMPRODUCT(I2:I11="Zone 1",J2:J11="Shift B",K2:K11,L2:L11) the answer will be "0". It is because the results of

I2:I11="Zone 1" = {TRUE, FALSE,TRUE, FALSE,FALSE....}

J2:J11="Shift B" = {FALSE,FALSE,TRUE,TRUE,FALSE.....}

4. To change TRUE into 1 and FALSE into 0, we need to use "--".

=SUMPRODUCT(--(I2:I11="Zone 1"),--(J2:J11="Shift B"),K2:K11,L2:L11)

--(I2:I11="Zone 1") = {1;0;1;0;0;.....}
--(J2:J11="Shift B") = {0;0;1;1;0......}

Note:

-(I2:I11="Zone 1") = {-1;0;-1;0;0;.....}
-(J2:J11="Shift B") = {0;0;-1;-1;0......}

5. Essentially the calculation is,

(1x0x15x15 + 0x0x15x15 + 1x1x15x20 +........)

6. This formula will give answer we need.

Love to have your comments....

How to use COUNTIFS

2012-06-24T06:58:00.000-07:00

In an earlier post I discussed about using CONCATENATE and COUNTIF to sum with two conditions. This can be done using the new COUNTIFS command.

Lets look at the following example:

I need to know the number of times machine 9 was used on 4th Jan 2011.

Use formula,

=COUNTIFS(F:F,DATE(2011,1,4),G:G,9)

You can use up to 127 criteria

DONE.

How to use SUMIFS

2012-06-24T03:02:00.000-07:00

In an earlier post I discussed about using CONCATENATE and SUMIF to sum with two conditions. This can be done using the new SUMIFS command.

lets look at the following example, if I need to know the quantity produced by machine 9 on date 4th Jan 2011.

Use formula

=SUMIFS(H:H,F:F,DATE(2011,1,4),G:G,9)

Done.

How to avoid BLANKS / errors in CHARTS

2012-06-17T04:39:00.000-07:00

Prerequisite

Able to use/create charts in Excel. Know how to use "ISERROR" formula. Please refer to Microsoft help for Syntax.

Possible Usage

To reflect a clean and accurate in formation in charts.

For example, refer to the table below.

If we plot Reject Percentage Vs Date we will get,

First the chart is not accurate as there was no production in certain days and reject rate cannot be considered "Zero".

Second the chart is not tidy.

The steps (How to do)

1. type =IF(ISERROR(C2/B2)=TRUE,#N/A,C2/B2) in D2

2. Drag or Copy/Paste all the necessary cells

3. We will get a clean and accurate chart

DONE

How to use SUMIF continued

2012-06-17T00:00:00.000-07:00

Prerequisite

Please refer to Microsoft help for Syntax.
=SUMIF(range,criteria,sum range)

Possible Usage

SUMIF is a versatile formula and can be used in different needs.

Example is using SUMIF in two column of data.

Need to calculate the quantity produced by machine 3.

The steps (How to do)

1. type =SUMIF(D:D,3,E:E) in B2

Other option is to type the machine number in one cell and use formula. For example =SUMIF(D:D,A2,E:E)
The formula can be used to calculate the quantity produced by every machine.

DONE

How to use SUMIF

2012-06-16T21:40:00.000-07:00

Prerequisite

Please refer to Microsoft help for Syntax.
=SUMIF(range,criteria,sum range)

Possible Usage

SUMIF is a versatile formula and can be used in different needs.

First example is using SUMIF in one column of data.

example

-5
10
-7
20
0.5
30
20
0.3
0.5
0.6
-10
10
20
-10

Need to add the values which are
1. less than zero
2. more than zero
3. in between 0 and 1

The steps (How to do)

1. type =SUMIF(A:A,"<0",A:A) in C1

2. type =SUMIF(A:A,">0",A:A) in C2

3. Type =SUMIF(A:A,"<1",A:A)-SUMIF(A:A,"<0",A:A) or =SUMIF(A:A,"<1",A:A)-C1

Done

How to use DATE, RIGHT and LEFT formulas to create date from unique IDs

2012-06-09T07:13:00.000-07:00

Prerequisite

Know how to use "DATE", "RIGHT" and "LEFT" formulas individually.
Please refer to Microsoft help for Syntax.

Possible Usage

In the earlier post we talked about how to create unique IDs using data code or time stamp. Here we will see how to do the reverse. This kind of situation does arises in Operation or Manufacturing environments.

The steps (How to do)

To create date we need year, month and day.

1. Type =LEFT(F2,4). Drag or copy paste the formula all the way (to cover the all the IDs). This will be the year.

2. Type =RIGHT(LEFT(F2,6),2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the month.

3. Type =RIGHT(LEFT(F2,8),2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the day.

4. Type =DATE(E2,D2,C2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the date.

DONE.

All comments are welcome.

How to use CONCATENATE, YEAR, MONTH, DAY and LEN formulas to create unique IDs

2012-06-03T04:28:00.000-07:00

Prerequisite

Know how to use "CONCATENATE", "YEAR", "MONTH" "LEN" and "DAY" formulas individually.
Please refer to Microsoft help for Syntax.

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.

Possible Usage

To create unique IDs using data code or time stamp. This is more applicable to operation or manufacturing environment.

example: create IDs using dates.

The steps (How to do)

1. type =YEAR(A2) in cell B2

2. type =MONTH(A2) in cell C2

3. type =DAY(A2) in cell D2

4. Add running numbers in column E. Use "text" format to make sure zeros appear in front.

5. type =concatenate(B2,C2,D2,E2). Drag or copy paste the formula all the way (to cover the all the dates)

Note that the ids are not of the same length.

To avoid you can use IF and LEN as described below.

2a. type =IF(LEN(MONTH(A2))=1,CONCATENATE("0",MONTH(A2)),MONTH(A2)) in column C

3a. type =IF(LEN(DAY(A2))=1,CONCATENATE("0",DAY(A2)),DAY(A2)) in Column D

The result will be as follows

Here we go nice and clean IDs.

Done.

All comments are welcome.