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 →
{ 0 Comments }
In range C4:C10, assume the following values/formulas 1. Cell C4 – 1 2. Cell C5 – 2 3. Cell C6 – W 4. Cell C7 – We 5. Cell C8 – Empty cell 6. Cell C9 =IF(D9+E9=2,”a”,””). Please ensure that cells D9:E9 are empty 7. Cell C10 =1/0. This will yield the #DIV/0! error To count numbers […]
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 →
Assume a database where customers are listed from cell A6 down. From cell B5 to the right months are entered from April to March (B5:M5). In B6:M6 (Customer 1), a user enters 1’s and 0’s. A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”. Similar data is entered for other customers in B7:M500. […]
Read More →
{ 2 Comments }
Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey. To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]
Read More →
{ 37 Comments }
Assume a two column database with airline names in column A and number of passenger seats in column B, one may want to know the total revenue. Given a full rate of US$ 12.0 per seat, discounts are offered on this full rate depending upon the passenger seats booked. So for example, for the first 500,000 […]
Read More →
Given the net annual salary figure and progressive tax rates for different income slabs, one may want to calculate the gross annual salary. You may refer to my solution in this workbook.
Read More →
With blanks appearing in a range of numbers, one may want to: 1. Average the first n numbers; and/or 2. Sum the last n numbers (in a horizontal and vertical range) Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached. You may refer to my solution in this […]
Read More →
Minimise the total number of inverters to be used for different electricity load factors
{ 0 Comments }