Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]
Read More →
Assume the following dates in range B4:B8 B4 – 29/09/2011 B5 – 04/10/2012 B6 – 05/10/2012 B7 – 07/10/2014 B8 – 15/07/2015 One may want to count dates which are within 365 days of each other. The answer should be four. The four dates are: 1. 04/10/2012 2. 05/10/2012 3. 07/10/2014 4. 15/07/2015 The formula […]
Read More →
{ 0 Comments }
In range B3:V3, one will record Purchase price of various Assets purchased in different months. In the next two rows one specifies whether the Assets purchased in that month have to be amortised or not and the months over which the Assets has to be amortised respectively. The task is to determine the amount to […]
Read More →
Assume a database of five rows and four columns. Each cell of the database has some text value – let’s call this the source grid. In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings – let’s call this the destination grid. The task […]
Read More →
{ 0 Comments }
Assume there are numbers typed in range B3:G8. In row 10, one may want to sum up diagonal cells i.e. in cell B10, one would want to get the value from cell B8, in cell C10, one may want to sum values from cells B7 and C8. in cell D10, one may want to sum […]
Read More →
Assume there are two types of inverters – Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW. For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used. Therefore the number of inverters which can be used for a load factor of 60kW […]
Read More →
{ 5 Comments }
Assume data is in range A3:E10. In A4:A10, various service types are mentioned. In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012. In range B4:E10 are numbers representing number of customers. One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]
Read More →
Assume the following data on a worksheet: 1. Cell B3 – Tenure of loan (in years). This will be a whole number, say 15 2. Cell B5 – Escalation (in percentage), say 10% 3. Cell B6 – Escalation period (in months), This will be a whole number, say 24. This means that the escalation will […]
Read More →
{ 206 Comments }
Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell). Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays. You may refer to my solution in this […]
Read More →
Assume a worksheet which has scores (1-10) on four future dates on nine questions. Each row represents responses for one question. Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet. The question and solution are more clearly explained in the attached […]
Read More →
Count entries in a range which exclude certain user defined words
{ 5 Comments }