First, Last, Min, Max for a particular value in an array

Consider a set-up where 4 users randomly appear for a test. Their scores are recorded in the order in which they appear for the test. Our objective is to find their scores in the first and last attempts and also their minimum and maximum scores. Here are the users and their scores:

The objective is to find the first, last, min and max score for each user. The solution looks like this:

For first attempt score, the formula is a simple vlookup

F2=VLOOKUP(E2,$B$2:$C$15,2,FALSE)

Person is B2:B15 and Score is C2:C15

For max score, the formula is simply:

H2 {=MAX(IF(Person=E2,Score,0))} entered as an array formula with ctrl+shift+enter

Similarly, for min score, the formula is

I2 {=MIN(IF(Person=E2,Score,100))} entered as an array formula with ctrl+shift+enter

I have entered 100 as it is greater than the largest number in Score. You may replace this with max(Score)+1.

Now for last attempt score, you could use one of the 3 sets of formulas

1. Using Index function – array formula

G2 {=INDEX(Score,MAX((Person=E2)*ROW(Person))-ROW(Person)+1)} entered as an array formula with ctrl+shift+enter

In this formula, max function finds out the last row number for a particular user. For instance for user Andy, MAX((Person=E2)*ROW(Person)) returns 14. To return the score for Andy in the last attempt, I have used index function. The minus part of ROW(Person)+1 simply takes care of cells above array Score.

2. Indirect-address function – array formula

G2 {=INDIRECT(ADDRESS(MAX((Person=E2)*ROW(Person)),3))} entered as an array formula with ctrl+shift+enter

If you feel uncomfortable with entering a number in the formula, you could replace 3 with column(Score)

3. Index – Sumproduct function

If you are tired of using array formulas, sumproduct offers some respite

G2=INDEX(Score,SUMPRODUCT(MAX((Person=E2)*ROW(Person)))-ROW(Person)+1)

The formula is similar to the Index with array formula. Here sumproduct is used differently then the normal usage. Notice that there is only one array in sumproduct. The function treats the parameters entered as an array, so we need not enter it as an array formula.

Similarly, indirect address formula can also be entered with sumproduct without entering it as an array formula.

Give it a try. Have fun 🙂

Answer:

Read Full Post »

Sum array with errors

How do you sum up an array which is a combination of numbers and errors? I faced this problem when carrying out a vlookup. The results_array is a combination of #N/A error and numbers as below:

On summing up Results_array through the formula

=sum(A2:A16) gives #N/A error.

To overcome this problem I made use of the following formula

A17  {=SUM(IF(ISNUMBER(A2:A16),(A2:A16),0))}

This is entered as an array formula with ctrl+shift+enter

The formula works in the same way as the results given in explanation array, where

B2=IF(ISNUMBER(A2),A2,0) and then summing up in B17

If you wish to count the occurences of numbers in the array A2:16 you could use this formula

A18 {=SUM(IF(ISNUMBER(A2:A16),1,0))}

Entered as an array formula with ctrl+shift+enter

Now some not so useful information 😉

724 incidentally is a sum of squares of 18 and 20. Try this =18^2+20^2
Or here is a function that you may use = SUMX2PY2(18,20)

Have fun!

 

 

Read Full Post »

Evaluating step functions

This problem is on step functions. Certain utility company charges me in this manner

For the first 25 units of consumption, the rate is 212 per unit, for next 35 units, the rate is 270 per unit and so on.

I have calculated the total amount payable for certain unit of consumption by using sumproduct and offset functions.

The formula to calculate amount payable is entered in C11 for consumption in B11

C11 =SUMPRODUCT(OFFSET($B$1,1,0,MATCH(B11,Cum_unit,1)),OFFSET($B$1,1,1,MATCH(B11,Cum_unit,1)))+(B11-INDEX(Cum_unit,MATCH(B11,Cum_unit,1)))*INDEX(Rate,MATCH(B11,Cum_unit,1))

The formula can be divided in two parts, the part in blue calculates the amount upto the upto the completed values given in cummulative units (Cum_unit) – 100 in the given example. Cost for remaining units (120-100=20) is calculated in the second part (formula given in green)

Cum_unit is D2:D8

sumproduct – sums up the products of two arrays [sumproduct({1,2},{3,4})=1*3+2*4=11]. This is exactly what we need to calculate the first part – {Units}*{Rate}

To find out how much of the array we need to sumproduct, match function is used. Match with match_type 1 is used for array Cum_unit, so that it returns a value equal to or lesser than the lookup_value. In our case it returns the value 3

Offset is used to generate the arrays for carrying out sumproduct, the reference is taken as B1, for Units array, row offset is 1, column offset is 0, height of array is given by the match function above – which is 3

For Rate array, row offset is 1, column offset is 1, height is same as given by match function.

The cost for remaining part is carried out through the use of index function on the two array Cum_unit and Rate and multiplying them

You would find this very useful when working on large units and rates arrays.

As always let me know if you find this useful 🙂

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions

The problem is given in the image sheet below

 

The H1Y1 performance of certain sales people have to be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

Read Full Post »

Handling errors in excel

I have listed down the types of error in excel and their likely causes in the table below:  

 

 Error type can be found out through the formula =Error.Type(cell_ref)

Count instances of error

One of the ways to count the total occurence of error in an area is this

{=SUM(IF(ISERROR(B2:B8),1,0))} entered as an array formula with ctrl+shift+enter

In the above example it returns 7

Replace error with a dash

If a formula gives you an error for certain input parameters and you do not want these to show up on excel sheet, you could use this function

=IF(ISERROR(formula),”-“,formula)

We have used this in creating spaces within a list using indirect function

Let me know if you need any help with these errors. Have fun handling errors 🙂

Read Full Post »

Insert spaces – revisited

I had earlier posted a solution on inserting spaces between continuous cells in a list. Though the solution works fine for a few items (46 items in the earlier example), it returns a blank for items after that. The solution had comprised of using the formula C1+1/3; where C1=1 and dragging it so that two fractional numbers are inserted between the integers i.e. 1, 1¹⁄3, 1²/3, 2 and so on. Excel processes these as recurring decimals and possibly rounds off recurring decimals. So the rounding off errors gets added up to the point where it is large enough for indirect function to fail.

Have a look at this

 

 

 

 

Where F2=INDIRECT(“A”&E2)

The indirect formula continues to work as long as the error ε<1E-15. Any bigger error returns a function error.

Have solved this problem through using a combination of int and mod, which works well for very large lists as well

The original list is given in array A2:A9 (which has also been defined as Original_list).

Enter B2=IF(MOD((ROW()-ROW($B$2)),3)=0,INDEX(Original_list,INT((ROW()-ROW($B$2))/3)+1),””)

Drag till the entire list is mapped

You could change the constant 3 to a higher number if you need to insert more spaces between the entries

Let me know if you find this useful 🙂

Read Full Post »

Troubleshooting vlookup

In this post I have listed issues that people often face while using vlookup

To begin with, here is the syntax for the vlookup function

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

This image shows how the function is used

[range_lookup] is an optional parameter and takes the values True or False

When the parameter is entered as True (or omitted)

• The array needs to be sorted in ascending order on the first column
• If exact match is not found, the next largest value that is less than look_up value is returned

When the parameter is entered as False, vlookup finds an exact match

1. On carrying out vlookup with range_lookup as false, the value returned is #N/A, though I am sure that the lookup_value is contained in column1 of table_array, what could be the problem?

One of the likely cause of this error is that lookup_value contained in table_array is preceded or succeeded by empty spaces or unseen characters. One way to find this out is through the function exact. You could key in =exact(lookup_value, corresponding value from table_array), if it returns a false, you know that they are not the same values. This often happens when you import a table from an external system. One of the ways to remove spaces is through Trim function. Refer an earlier post on how to trim

2. Vlookup returns a wrong corresponding value from the table_array.

The most likely cause is that you have either entered range_lookup as true or omitted it and that table_array is not sort in ascending order on column1. Either enter range_lookup as false, or sort table_array in ascending order on column1.

3. I am looking for an approximate value through vlookup with range_lookup as true. Have sorted the table_array in ascending order, yet vlookup returns #N/A. Can you figure out what could be wrong?

vlookup with range_lookup true uses the largest value in column 1 of able_array that is less than or equal to look_up value. So if look_up value is say 50 and the table array starts with 100, then vlookup would return an error #N/A

 For instance if lookup_value is 155,

vlookup(155,A2:B5,2,true) would return 2.04

4. I am carrying out a vlookup with a 10 column table_array, vlookup returns a #REF! error.

Make sure that col_index_num does not exceed 10 and you should be fine.

General observation: When the table_array is stored in a separate workbook, make sure that workbook remains in the same location as when the vlookup was created

Let me know if you have faced any other kind of problem while using vlookup.

Read Full Post »

Updated some posts

Have updated the posts on Multiple extraction through vlookup with two alternative solutions with use offset function and index.
Have also updated the post on Separating names into last and first name with an alternative solution that involves manually splitting names using Text to Columns..

Read Full Post »

Unraveling a table

Here is the problem that I faced

 

 

 

 

I had to extract the entries in the table row-wise and populate this in a single column.
Here is one way of doing this:

• Enter A8=OFFSET($A$2,INT((ROW()-ROW($B$8))/5),MOD((ROW()-ROW$B$8)),5),1,1)
• Drag down till the entire table is unravelled.

What if we had to unravel the table column wise i.e. in the order A2, A3, A4 etc

Here is the formula that you need to enter in B8 and drag till the last table entry

B8=OFFSET($A$2,MOD((ROW()-ROW($B$8)),3),INT((ROW()-ROW($B$8))/3),1,1)

One could also extract the table in a columnar format. We would have to switch row for column to do so.

mod has the syntax mod(number, divisor) and returns the remainder when number is divided by the divisor. In the above case when the divisor is 3, it returns 0,1,2,01,2 and so on

int(number) returns the nearest integer less than or equal to the number. In the above case int returns 0,0,0,1,1,1,2,2,2 and so on

This is exactly what we need to offset from the reference in the table and unravel it.

Note the divisor in the two formulas, in the first formula the divisor is 5, which is equal to number of columns in the table

In the second formula divisor is 3, which is equal to number of rows in the table

This could also be computed through columns(A2:E4) and rows(A2:E4) respectively

Let me know if you ever find this tip useful or are not clear about any part of the post.

In the meantime, have fun working with excel 🙂

Read Full Post »

Generating a series of numbers

In some of my earlier posts, I have manually populated some cells with a series of numbers (numbers 1-4 were manually populated in the post multiple extraction through vlookup). Here is a way we can do away with this by using functions row() and column()

Lets say you want to populate 0 to 10 in a column in cells B5 through L5:
Enter B5=COLUMN()-COLUMN($B$5)

On dragging upto L5, it populates the cells with the series 0 – 10.

As you can figure out

• column() returns the column number of that particular cell
• If a reference is provided [eg. column(B5)] – it returns the column number of the referred cell – 2 in the above example

If we were to populate numbers in a row, the function row() could have been used. To populate cells A1 to A11 with numbers 0 to 10,

• A1 = row()-row($A$1) – drag till cell A11

’tis done!

§ I am going to leave an example for you to think about the utility of this. Also would use this function in my next post.

√ To sum first 10 natural numbers – 1 to 10 i.e Σ(1,2..10)

Enter this in any cell =SUM(ROW(A1:A10)) as an array formula i.e. ctrl+shift+enter instead of enter. This gives the result as 55

The formula is interpreted as SUM({1;2;3;4;5;6;7;8;9;10})

If you found this interesting or would like some more help on this, let me know 🙂

Read Full Post »