Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook. The task is the compute the MODE of these numbers. Mode is defined as the value which appears most frequently in a range of cells. So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in […]
Read More →
Assume per diem travel rates vary by block of dates (from and to). So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day. Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day. With different travel dates (from and to) specified per traveller, the […]
Read More →
{ 0 Comments }
Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions. Each Region has its own worksheet. There are three headings on each sheet – Particulars, 2009 and 2010. While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]
Read More →
{ 2 Comments }
In a range of cells, assume there is a list of invoices raised during a certain period. Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range. So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]
Read More →
In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched. Furthermore, the VLOOKUP() function returns data from one column only. To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, […]
Read More →
{ 0 Comments }
A number is entered in cell A1 of every sheet in a multi sheet workbook. The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]
Read More →
Assume a worksheet with formulas referring to the previous worksheet. When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet. In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by […]
Read More →
Assume data in range B4:B18 (heading is in cell B3). To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down =B4=B5 True’s indicate matching values with the value in the next row. However, the above formula would fail when […]
Read More →
{ 5 Comments }
Suppose a worksheet name is Code_details. The following values lie in range A2:A14 of this sheet: A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123, ASDFG1234567890 and ASDFG123456789 Suppose there is another worksheet named Summary where the following values lie in range B5:B7: ASQ47809876,ASW675458 and QWERT Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet. If this exact value is not found there, then the […]
Read More →
{ 10 Comments }
Assume a matrix like data layout where Site’s are mentioned in range C2:C15 and first day of every month from January 2011 till April 2012 are mentioned in range D1:S1. In range D2:S15, the numbers represent the value of the electricity bill received in that month. Some cells can be blank representing bills not received in that […]
Read More →
Compute MODE of all numbers split across multiple worksheets
{ 10 Comments }