Question 2 - Standardising spreadsheets
Can you help me standardise the spreadsheets which i maintain for recording the interview results of candidates?
From: Venkatanarasimha KL
Workaround
Herein, i would like to emphasise the importance of the good "look and feel" of spreadsheets. While it is possible to employ functions and formulas to achive your end objective, a well structured spreadshet enhances the presentation. Please feel free to view the original spreadsheet submitted and my solution.
Report a bug/suggest refinements
Question 3 - Searching for data in multiple columns
Where the data to be searched is spread across multiple columns, is it possible to search those multiple columns for occurence of a string and then return the amount against it?
From: Unknown
Workaround
The index function returns the data at the intersection of a certain row and column. However, if the data being searched in the list is spread across multiple columns, the index function would return an error. This is one workaround to the problem.
Report a bug/suggest refinements
Question 4 - Linking cell to tab name
Is it possible to link a cell to the tab name so that whenever i change the tab name, it is reflected in that cell also.
From: Unknown
Workaround
This is also important to do where the person wants to review a print out of the workbook rather than the soft copy. In such a scenario, the worksheet's name on the top right corner would tell the reviewer which sheet he/she is on. Here is a workaround.
Report a bug/suggest refinements
Question 5 - Dynamically copy unique cells to a different range
In my list of data, i usuallyhave duplicate data. I was just wondering if it is possible to remove the duplicate data and paste the unique values only.
From: Unknown
Workaround
This array formula helps you achieve just that.
Report a bug/suggest refinements
Question 6 - Count unique values in addition to satisfying two conditions
I have a list of data in which i want to count the amounts against satisfaction of two conditions. The additional complication here is that i want to count only the unique amounts?
From: Unknown
Workaround
In this problem, you actually have 3 conditions to meet -
a) Condition 1;
b) Condition 2;
c) Count unique values only.
Please see my workaround to this problem.
Report a bug/suggest refinements
Question 7 - Return upper case letters from a string
Hi, i saw your article on Office online which described the formula to extract the numeric portion of an alphanumeric string. Is it possible to return the upper case alphabets from a string i.e from a string such WRe234, can we extract only WR.
From: George de Beer
Workaround
I have tried to come up with a workaround to this situation. However, please note that this formula would only work for strings where the upper case letters are continuous such as WER34, 56YUI, 78TRe etc. It will not work for WeRT56. Here is my workaround to the problem.
Report a bug/suggest refinements
Question 8 - Horizontal Lookup
Hi, the attached spreadsheet has 4 columns with numeric values in it. To the right of each column containing numeric values is a column containing a letter of the alphabet (alpha value). The formula needed in Column " J" is to return for each row the "alpha value"that is entered in the cell 1 column to the right of the cell that has a "numeric value" of "1". I have completed the first 4 rows. For each row there will be only one cell with a value of"1". As a visual aid.this cell has been highlighted in "Tan". Return in Column J the "letter of the alphabet' that is entered in the cell 1 column to the right. As a visual aid this cell has been highlighted in "Gray".
From: Lou
Workaround
Hi, in a situation like this, it is best to employ the hlookup function. Please view my solution here.
Report a bug/suggest refinements
Question 9 - Parsing strings using formulas
Hi, my accountant has entered the product codes in one cell instead of entering them in seperate columns. He has used the following format to enter the product codes - code1, code2, coden. Is it possible to parse the string so that i can obtain the codes in seperate columns? Also, is it possible to automate this whereby for any string (of the aforementioned format) which gets added, the formula automatically parses it into different columns.
From: Newsgroups
Workaround
Hi, in a situation like this it is ideal to use Excel's built in "Text to columns" feature available under the Data menu. However, since you would like to automate the parsing process, i have tried to formulate a workaround to the problem.
Report a bug/suggest refinements
Question 10 - Proximity Search
Hi, i have a list of numbers in an Excel worksheet. I would like to return the smallest and largest numbers in the range which are closest to a number defined by a user. For e.g. if the range has numbers such as 1,2,8,10 and the user enters 9 in a cell, then i want the closest number to be 8 and the largest number to be 10.
From: Newsgroups
Workaround
This is quite different from a conventional problem whereby the user desires to search the minimum and maximum number in a certain range. Herein you want the minimum and maximum based on a user defined proximity number. To perform such a proximity search, please refer to this workaround.
Report a bug/suggest refinements
Question 11 - Count unique values with conditions
Hi, while i know how to count unique values in a range, is there a way i can count unique for certain user specified condition(s). For e.g., If in a range i have months in column 1, port numbers in column 2 and Names in column 3, then "How can i count unique values for a certain month and for a certain name and month".
From: Newsgroups
Workaround
Hi, this is an interesting question. Counting unique numbers in a range is quite a commonly question discussed in newsgroups but this is truly a unique problem. After fair enough deliberation, this is the solution i could come up with. In my workbook, i have first presented 2 solutions - one for counting unique value based on one condition and the other for counting unique values based on 2 conditions .
Report a bug/suggest refinements
Question 12 - Vlookup on a certain portion of a text string
Hi, is it possible to perform a vlookup on say that last character of a text string instead oif the whole string. For e.g., if in column A, all my text entries end with a number, then can i specify vlookup to search for the amounts outstanding against these text entries.
From: Newsgroups
Workaround
Hi, I have taken a similar example to yours to demonstrate how we can solve this problem. Please find a workaround to this problem.
Report a bug/suggest refinements
Question 13 - Extract the uncommon portion in two strings
Hi, i have two columns of text and i desire to compare the two. The final result should be the uncommon portion of these two strings. For e.g., if in column B i have a string ABCDE and in column A i have CDE, then in column C the result should be AB.
From: Newsgroups
Workaround
Hi, this is truly an interesting question. I took quite some time to come with a workaround this. Please find my solution herein. I have hidden the working column (column C to G) used to arrive at the solution. If you wish to understand the solution, please unhide these columns.
Report a bug/suggest refinements
Question 14 - Auto sort figures in ascending order
Hi, i have a list of figures which i want sorted in ascending order. Currently i sort the figures through Data>sort but i find it fairly cumbersome to do so time and again when the original list changes. Is there an auto sort function in excel?
From: Newsgroups
Workaround
Hi, i absolutely agree with you on how cumbersome the process of sorting can be. Here is a simple workaround to the problem. When you change the fugues in range A3:A9, the list would get auto sorted in range B3:B9.
Report a bug/suggest refinement
Question 15 - Fuzzy addition
Hi, is there a way i can perform fuzzy additions. What i mean is that, if in a range i have fruit names in column A and in column B i have amounts, how can i sum up the amounts against specified fruits. The complication here is that the fruit against which the amount has to be added may either appear independently in a row or may be clubbed with some other fruit.
From: Newsgroups
Workaround
Hi, please find herein a workaround to this problem. It is a simple array formula solution.
Report a bug/suggest refinements s
Question 16 - Copy information from cells above
Hi, I have a speadsheet in which I have to manually copy and paste missing information. For example, the following cell that is blank below should also indicate A123 and 345, W234 and 780.(whatever info that is above it). I usually have to manually cut and paste that information. My problem is that I have such a big file ..it takes me forever to do it. Is there a quicker way? Please advice.
Product brand Product id Ent id
A123 345 a345
a780
W234 780 w451
t659
From: Audrey
Workaround
Hi, I can well imagine how painstaking it would be th copy and past information manually. Please follow the following steps to automate the process of filling information in the blank cells in column A and B.
1. Select the data in column A and column B. Navigate to Edit>Go To>Special. Now check the Blanks check box. You will now notice that all blanks cells would be highlighted.
2. Now press = and then the up arrow key
3. Lastly, while holding down the Ctrl key press enter.
You will now notice that all the blanks would get replaced with the number above.
Report a bug/suggest refinements
Question 17 - Sum highest n numbers based on a certain condition
Hi, i have a list of names with figures against those names. The list has repeated names and i am trying to sum the highest 3 values for a particular name. Can you help me?
From: E Carol Vojtila
Workaround
Honestly this was a fairly challenging problem for me to solve. This is a fairly novel problem in as much as it involves the additional complication of summing up only for a certain name. Please feel free to review my workaround.
Report a bug/suggest refinements
Question 18 - Extract names (4 cases discussed)
Hi, is there a way to extract names into first/second and third name in Microsoft Excel. I basically need this to segregate the names of Individuals into different cells so that i can sort by surname/first name etc. While i know that data parsing feature in Excel, the problem with that built in feature is that one has to do it time and again for all names added.
From: E Carol Vojtila
Workaround
In my workaround, i have discussed four different situation for extracting first/last name. Please feel free to go through it.
Report a bug/suggest refinements
Question 19 - Display numbers in Indian currency format
Hi, can i depict the commas in a number such that after the first three digits, commas appear after every 2 digits. For e.g. 123456789 should appear as 12,34,56,789.
From: Satti Charvak
Workaround
Hi, you may achieve this through Excel's custom number format. Hightlight the range of numbers and then Navigate to Format>Cells>Number>Category>Custom. In the "Type" box, copy the following format
[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00
Lastly, i would like thank Bob Phillips for this solution.
Report a bug/suggest refinements
Question 20 - Reduce worksheet and workbook scroll
In a large worksheet or a workbook with many sheets, one of the most time consuming tasks is plodding through them to check a certain number. In such cases, we can save a lot of time by employing the F9 shortcut key. For a formula such as =IF(O54>123,1,0), if one wants to view the data in cell O54 then instead of scrolling to the particular cell, one can just press F2 (in the cell which houses this formula) and highlight O54. Then press F9 to view the data of cell O54.
Report a bug/suggest refinements
Question 21 - Conditional count
Need your help in resolving the following
Col A Col B Col C
a 1
a 0
a
b
b 2
b
c 1
c 2
c 3
My question is how to count the no. of cells with values in column B basing on the condition at every change in column A.
From: Vijay Syam Kumar Damaraju
Workaround
Hi, just enter the following array formula (Ctrl Shift Enter) in cell C1 and copy downwards
=COUNT(IF(($A$1:$A$9=A1)*($B$1:$B$9<>""),$B$1:$B$9))
Question 22 - Average ignoring blanks
I want excel to calculate average expenses across months of the year, ignoring months that have not yet been filled in. Here is the formula I am using:
{=IF(B10:M10=0,"",AVERAGE(IF(B10:M10<>0,B10:M10,"")))}
The problem I am having is that the average is only displayed if I make an entry into the first cell of the range, in this case B10.
On my sheet, B10 corresponds with the month of January and since we are currently in the month of November, I want to leave it blank.
My question is this: How can I get the average to be displayed without necessarily filling in the first field of the range?
From: Joe Becker
Workaround
Well you just have to enter the following formula =SUM(B9:M9)/COUNTIF(B9:M9,">0"). This will calculate the average of all the months in the year where the figure is greater than 0.
Report a bug/suggest refinements
Question 23 - Keep one sheet on the workbook always visible
Hi
Kindly help me out with this query.
I want to show two workbook simultaneously in a window. In the sense that as i open the file, i should get two workbooks; one showing only the sheet which contains TOC and the other containing the contents/other worksheets... Essentially for easy navigation...I hope i am clear in puttingmy query forward..Is it possible to do so?
From: Saurabh
Workaround
Please follow the undermentioned steps to achieve the desired result:
1. Open the workbook and click on the TOC sheet;
2. Navigate to window>New window. You will now notice that another wokbook would open with the following name - [workbookname]:2. The original workbook would be named [workbookname]:1. Keep the [workbookname]:1 activated
3. Go to Window>Arrange and click the Vertical radio button. This will arrange the two wokbooks vertically.
4. Adjust the window width of the two workbooks
5. Now go to Tools>Protection>Protect Workbook
6. Keep the "Structure" checkbox checked
7. Key in a password so that no one can close either workbook
8. Save the workbook
Now when you open the workbook, you will notice that the ToC sheet will always appear on the left.
Report a bug/suggest refinements
Question 24 - Insert current time in a cell
Hi, i am stuck with a scenario where I want only specified key strokes as inputs. Ctrl Shft ; . This will ensure that only current time is keyed in. it should not accept any other value as input...
Is there any way where we can make sure that the cell accepts only selected combination of key strokes as inputs?
The following 2 solution would not be useful.
Solution 1
1. Highlight the cell where you want to restrict the entry of time via Ctrl Shift ;
2. Go to Data validation
3. In the Settings tab, under the Allow drop box, select "Time" and under Data select "between"
4. In Start time, type 12:00 AM. In End time, type 12:00 PM
5. Click on OK
This option would not help my purpose as I do not want ppl to enter any other value except the current time.
Solution 2
1. Go to Data>Validation
2. In Allow drop down select "time" and in Data select "between"
3. In start time, type =now(). In end time, input =now()
4. Click OK
5. Now press Ctrl 1 (format cells)
6. On the number tab, go to the "Custom" category and on the RHS window, select the "h:mm AM/PM" type. Click OK
7. Now the only input allowed in the cell will be =now(). No other value can be input in this cell
This solution would also not help because the moment you change one cell, all the data keyed in previously by using =now() will change to the latest keyed in time, making the entire exercise faulty. I basically want the time entered to be frozen.
From: Vinit Merchant
Workaround
Apply the following data validation to cell A1
=ABS(A1-(NOW()-INT(NOW())))<(1/(60*24))
The 1 is the number of minutes that the entry can vary from the current time - increase that to make manual entry a little easier. Change the input messages, etc..
Now you can enter the time in the cell either by Ctrl Shift ; or manually punching it. However, the manually punched time will be accepted only if it varies by 1 minute from the current time. You may change the 1 to what ever you wish to.
I would like to thank a fellow Excel MVP (Bernie Deitrick) for providing this solution.
Report a bug/suggest refinements
Question 25 - Extract e-mail address from a text string
Hi, is it possible to extract an e-maiil address from a text string. The e-mail address could be placed any where inthe text string i.e. its position is not fixed.
From: Mahendran
Workaround
Please find attached my workaround in the attached workbook. You may input the text in column A. As of now, i have assuemd that the user would enter text till cell A23 only. However, if you wish to enter more text, then please copy range J23:Z23 downward. Herein i have assumed that the number of words (including spaces) before the e-mail address are upto 12. I have also assumed that the "@" symbol is used only in the e-mail address nd nowhere else.
Please feel free to share your thoughts on the same and request any modifications (to suit your requirements).
Report a bug/suggest refinements
Question 26 - Extract number from an alphanumeric string - Part I
Hi, the format of my alphanumeric string is Product#, -, #two Note(s). This can be viewed in sheet "Tabular with #s".
Some examples are 61757-1980; 61758-1700 all of it; 61760 - 500; 56423-1250 urgent; 61757-1380; 61758-820; 61757 - 940; 61758 - 1260.
The portion that I need to extract is the product number as a number. Originally, the data always started with a space. Now, as you can see, the alphanumeric strings can start with a space or a number, and can, but do not necessarily have spaces in between the numbers and the dash. Do you have any suggestions how I could alleviate this problem?
From: Dennis
Workaround
Please find attached my response to your question in sheet "Tabular Schedule" of the attached workbook. I think the formula now takes care of all types of inputs. In the range C3:F30, i have hidden the error values by using conditional formatting.
Question 27 - Extract name into first name, middle name and surname
Hi, i have a workbook where first name, middle name and lastname are entered in one cell. I was wondering whether there is a way to segregate the names (first, middle and surname) in 3 different columns.
From: Padmanabha Vyasamoorthy
Workaround
Please find attached my response in the attached workbook (Range J2:L204).
Report a bug/suggest refinements
Question 28 - Extract number from alphanumeric string - part II
Hi, i have a workbook with alphanumeric strings. I would like to extract the numeric portion os the string. The numeric string may or may not have decimals. For numbers which have a decimal string, I would like to extract the entire number (including the decimal string).
From: Kushi Wije
Workaround
Please find attached my response in the attached workbook (Range B1:B5).
Report a bug/suggest refinements
Question 29 - Return closest match
Hi, please find herein a workaround to returning match in a list. For values which are not in a list, the vlookup function returns the closest match less then the vlookup value. However, this workaround returns the closest value (greater than or less than) to the lookup value.
Report a bug/suggest refinements
Question 30 - Copy information from various worksheets without going to them individually
Hi, many a times we need to a create summary sheet for summarising the various Excel worksheets we have created in the workbook. This entails going to each sheet and linking cells manually. While this may be OK if there are limited number of worksheets in the workbook, it may become quite a daunting task to link information from 25 - 30 sheets. Not only would this task be time consuming (go to each sheet sheet and link data) but also error prone.
Please find herein a workaround where one can avoid manual linking. I have created formulas in range G5:Q6 of sheet "Summary" which will automatically pick data from the sheet name which is specified in range E5:E6 (under heading "Investee") of the same sheet. Please keep in mind that the names in range E5;E6 have to exactly the same as the name of the worksheets.
To try out the formula, copy the range G6:Q6 to range G7:Q7. you will notice that the information from sheet "GHI" would be picked up.
I hope you find this tip useful.
Report a bug/suggest refinements
Question 31 - Conditional Input
I want to use (say) cells A1:A10 with data validation (list type) having options 'X' and 'Y'. Now when 'X' is selected in cell A1, cell B1 should allow me to input text or data; and when I select 'Y' in cell A1, cell B1 should have data validation (list type) having options (say) 'K', 'L' and 'M'. So, depending on the selection (from list) in cells A1:A10, cells B1:B10 will either 'allow me to input text or data' or will 'have data validation list'.
From: Kartik
Workaround
Hi, please find attached my workaround in the attached workbook. I have basically used data validation to restrict entry in column B in cases where the value entered in column A is Y. I have also used Excel's Name feature to accomplish this task.
I hope this solves your problem.
Report a bug/suggest refinements
Question 32 - Generate a set of unique random numbers
Hi, how can i generate a unique set of random numbers in Excel i.e. there shold not be a repetition of any number in the random numbers generated by using either the random, randombetween function or the Random number generator tool in the Analysis Toolpack.
From: Sammuel
Workaround
That's a good question. There is no inbuilt functionality to generate a list of unique random numbers. However, my workaround can be of assistance in solving this task. I have demonstrated the solution by first generating 25 random numbers (integers) using the randbetween function (range A2:A26). From this list so generated, i have extracted the unique numbers in range G2:G26. I have hidden the 2 helper column (Column E and F).
I would just like to advise you that the number of unique random numbers which you would like to generate in column G should determine the quantum of random numbers which you should generate in range A i.e. if you would like to generate 30 unique random numbers in column G, then i would suggest that you first generate at least 60 random numbers in column A first.
Report a bug/suggest refinements
Question 33 - Make a hyperlink to a specific worksheet/named range in a different workbook
If you wish to make a hyperlink to a specific worksheet of another workbook then you can select the specifc worksheet from the "Bookmark" tab. In the bookmark tab, you may also select a specifc range inthe other workbook if that range has been defined by a specific name.
Report a bug/suggest refinements
Question 34 - Making Floating bars
Hi, i want to construct a floating bar graph and show both the minimum and maximum values on the extreme positions in the graph. While I can construct the floating bar graph from Excel’s Custom type graphs, I cannot make the values appear on the extreme positions in the graph. I was wondering if there is a workaround to this problem?
Please find attached my workaround to the problem.
Report a bug/suggest refinements
Question 35 - Compare lists with conditional formatting
Hi, i have two list in column A and B. I would like to conditionally format the data in column A if the same figure is found in column B. So i essentially want to compare each figure in column A with the entire list of column B.
From: Zafar Iqbal
Workaround
Please find attached my workaround to the problem.
Report a bug/suggest refinements
Question 36 - Compute the future value of a series of different payments
The FV() function returns the future value of an annuity (equal payments which do not vary over a period of time). However, if one wants to compute the future value of a series of payments which are not equal, then the FV() function would not work. Please find herein a solution to this problem. Solutions II and III give the answer without the helper column.
Question 37 - Randomly select from a list of names
I am told that I can use randbetween() to select one name from a list of names, I have tried it and it does not work. Please help!
From: Susan
Workaround
Please find attached my workaround to the problem.
Report a bug/suggest refinements
Question 38 - Return lowest value which is higher than a specified number
Hi, in a range of numbers, how can i return the lowets number which is higher than a specified number. I tried using the VLOOKUP() command but that did not help. Please advice.
From: Poovelan
Workaround
Please find attached my workaround to the problem. I have presented two solutions here - array and non array.
Report a bug/suggest refinements
Question 39 - Make the dates and cash flows in an XIRR() function variable
Hi, in the XIRR() function i would like to make the dates and cash flows variable such that when the user changes the dates, the cash flow stream changes accordingly.
From: Rocky Lopez
Workaround
Please find attached my workaround to the problem. As and when the user changes the dates in cells A9 and B9, the XIRR() result would change in cell C9.
Report a bug/suggest refinements
Question 40 - Return the smallest number which is larger than a user specified number
Please find here in a workaround to return the smallest number which is larger than a user specified number.
Question 41 - Perform lookup on multiple variables
Hi, in the BIHAR workbook, i want the number of negative growth branches to appear for Bihar, Jharkhad and Orissa and within each of these states, i further want the break up between Metro, Urban, Sub urban and Rural.
From: Anil
Workaround
Please find attached my workaround to your question.
Report a bug/suggest refinements
Question 42 - Summing based on conditions
Hi, say I prepare a report for dept. wise (approx 20 depts.) on analysis of expenses budget vs. Actual for Feb-07 and I want to show only columns for the month of Feb-07 Bud & Act and Cumulative Feb-07 Bud & Act in total 4 columns representing the figures.
Here, I prepare the same report every month. So I need to change cumulative formula every month to get the correct value. If I wish to view any particular previous month also, I need to change it again and again.
If you can help me on this it will be great time savings on part everymonth with almost nil chances of mistake.
From: Jignesh Joshi
Workaround
Please find attached my workaround to this problem. As and when you change the month number in cell B2, the result in column C and D will change.
Question 43 - Calendar feature in Excel
Hi, please find herein the solution to have an interactive calendar in Excel - the procedure is outlinedin the atached workbook. Solution adapted from John Walkenbach's book.
Question 44 - Make the entries in a data validation list operational by their first letter
Hi, I have a very large list of entries and i would like to make the same available in a data validation list in Excel. The problem there is that the user would have to scroll throug hthe entire list to select the desired entry. What i would ideally want is that the user types the first alphabet of the entry and he is taken to the first word in t he list which starts with that alphabet. This will save considerable time for the user since he waill not have to scroll the entire list.
From: Sridhar Jaganathan
Workaround
Interesting question and also a tough one to solve. Please find attached my workaround to your question. The drop down list in cell C2 is the normal drop down list created through data validation. This list does not have your desired functionality. However, the drop down list in cell E2 has the functionality you are talking about. In cell E2, type the first letter of the any alphabet and then click the drop down button in the cell. You will be taken to the first alphabet int he list. The workings for the drop down list in cell E2 are provided in the hidden columns - G:O. Please note that in this solution, i have not factored in addition of any further entries in the original list in column A. However, that should not be very difficult to incorporate.
Question 45 - Concatenate data from different columns in one column but in different lines
Hi, i have a person's address split across cells A1, B1, C1 and D1. I would like to aggregate the data in these different columns into one cell (E1). Also, i would like tht data to appear in different lines of the same cell (E1). Is this possible?
From: Darpan Dureja
Workaround
Interesting question. Most people usually have the question reversed i.e. how to split data which resides in different lines of one cell into different cells. The solution to your question is as follows - In cell E1, enter the following formula =A1&char(10)&B1&char(10)&C1char(10)&D1. Now go to Format cells > Alignment tab and check the Wrap text box.
Question 46 - Extract text from a comment box into a cell
Hi, is there a non VBA/non macro solution to extracting the text written in a comment box to a specific cell.
From: Nitin Dubey
Workaround
Hi, please follow the procedure outlined below to extract text from a comments box:
1. Select the cell which has the comment;
2. Go to Insert>Name;
3. Now insert the following formula in the "Refers to" box - get.object(12,"comment 1") and give it a name, say trial;
4. In any cell (say A5) where you want to insert the contents of the comments box, say =trial.
Please note that whenever you change the text of the contents box, you would have to update cell A5 by pressing Ctrl+Alt+F9. This is so because this is a custom worksheet formula swhich uses XLM 4.0 macros.
Question 47 - Dynamically transpose approximately 12,000 rows of data into columns
Hi, I want to have your help in sorting the data in the “Sample” tab – The Column A represents “Heat ID”, Column B represents “Sample ID”, Column C represents “Element ID”, and Column D represents “Values of Elements as identified in Column C”. The values of Column D correspond to Column A & Column B. I want to have these values in a single row for corresponding “Heat ID” & “Sample ID”.
I hope to get help from your end – I want to do this in a simple way. I can do this by “Copy” & “Paste Special – Transpose” but difficult to do when I am trying to sort thousands of data.
From: Sabyasachi Bandyopadhyay
Workaround
Please find attached my solution to your question in range I1:AD11. I have deleted rows beyond row 241 to contain the file size - however, the formulas for additional entires would be the same i.e. as and when you add more entries, please copy the formulas F2;AD2 downwards. Please note that my workings are hidden in column F and G. You can click on the plus sign (above column H) to view the workings. Also, in your original file, since column A figures were not formatted as numbers, i converted all the values to numbers by multiplying them by 1.