I have altered a statement I got from a previous answer a bit and it now looks like this: =IF(C6=$R$3,IF(D6<=0.99,$U$2,IF(AND(D6>0.99,D6<=4.99),$U$3,IF(AND(D6>4.99,D6<=14.99),$U$4,IF(AND(D6>14.99,D3<=29.99),$U$5,IF(AND(D6>29.99,D6<99.99),$U$6,""))))),$S$8) It all works fine until you change the value in cell D6 to say £45 when it still...
I am using C++ and I am generating a csv file to report some of my testing output. There are some 1000 odd test iterations and each iteration output consumes a row in excel. The reason I use CSV file is so that I can use excel sheet to open it and view the outputs. Now, I would like to use the formula capability of excel sheet thru CSV....
I've got a spreadsheet like this: date | 7/1 | 7/2 | 7/3 | 7/4 -----|-----|-----|-----|----- val | 3 | 5 | 1 | 3 -----|-----|-----|-----|----- I want to sum the val row, but only up to the current date. So if today were 7/3, the sum would be 3+5+1=9. If today were 7/4, it would be 12. I figured this out to get the number of...
I have data of program usage in excel file, the data has date and time. I want to summarize this information that in each day each hour, how many user use this application. How to do that using excel formular? sample data: Jun 01, 09:03AM Jun 01, 10:00AM Jun 01, 10:50AM Jun 01, 11:00AM Jun 01, 11:26AM Jun 02, 01:00PM Jun 02, 03:00PM Ju...
Hi all, Can I get your help on some Maths and possibly Excel? I have benchmarked my app increasing the number of iterations and number of obligors recording the time taken in seconds with the following result: 200 400 600 800 1000 1200 1400 1600 1800 2000 20000 15.627681 30.0968663 44.7592684 60.9037558 75.8267358 90.3718977...
I need to return an empty cell from an Excel formula, but it appears that excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I want need something like =IF(some_condition,EMPTY(),some_value) I tried to do things such as =IF(some_condition,"",some_value) and =IF(some_con...
Hi, is there a short formula to get the xth letter of the alphabet? So if I give parameter '5' to the function, I would get the letter 'e'. thanks! ...
I'm creating a "one-period" financial model in Excel - that is, a model where the formulas are consistent in each column, so you can copy/paste/extend them as desired. I'm breaking out the model's assumptions into a separate sheet. For instance, I have a cell named monthly_sales; that's how many new customers I add each month. The formu...
I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array). I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1. Likewise, I would expect AVERAGE(A1)...
This isn't really a question, more of an issue I ran across that I hope others can avoid. If anyone can test in Office 2002, 2007, and 2008/Mac, that may add some important context. Before replying, please note that Microsoft has released two XML-based formats for spreadsheets: SpreadsheetML and Office Open XML (aka "xlsx"). I've only ...
I could not find the function that returns the name of the cell referenced. For example, =SOMETHING(A5) that will return A5 as string. So that, I can write ="For more information, see " & SOMETHING(F50) & "." in a cell and the cell will read For more information, see F50., and then, when I move the F50 cell, the text will also change. ...
Hi there, I'm wanting to SUM a number of rows in Excel, based on a value. In the picture below, you can see the table. I want to put a value in F1, containing the sum of the values for Jan -> (month # specified in C2) where the Status is the value in C1. If you do =SUMIF(B5:B11,"open",INDIRECT(CONCATENATE("C5:",ADDRESS(11,2+C2)))) ...
Hi all, I was tasked with counting the number of distinct strings in a column in excel. A quick Google search later yielded the following formula found here: =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) Consider the data: A B C D A B E C Now, the match function would return an array (as the first argument...
Do you know a way in Excel to "calculate" by formula a list of unique values ? E.g.: a range contains values "red", "blue", "red", "green", "blue", "black" and I want to have as result "red, "blue", "green", "black" + eventually 2 other blank cells. I already found a way to get a calculated sorted list using SMALL or LARGE combined with ...
Is there a way using a cell formula in Excel to find the index of the first non-alpha character in a referenced cell? I've imported data into Excel which has a column that looks like this: <some text I don't want in the derived cell><some text that prepends the text I want and is always constant>TheTextIWant<Some non-alpha character><s...
Is there a way to add a leading zero to a date that is 7 digits and should be 8? 7301982 should be 07301982. I have a column full of these values, and need a way to do so with a formula. Any ideas? ...
I'll simplify this for example sake I have a formula in column B which is Ax + 2, e.g. in B1, it's A1 + 2 in B2, it's A2 + 2 I want to make a formula in column Y which mirrors whatever formula is in column B at all times but replaces the A in the formula with a X. e.g. in Y1, it's X1 + 2 in Y2, it's X2 + 2 No matter what...
Hello ! I have two columns with two values.. I want to append some text to the left of all the cells of Column A and concatenate to the right of all the cells of Column B. Basically I'm trying to avoid having to type in a whole bunch of sql updates. so for example if i have an excel with ID Employee Name 135 Rasputin 76 Bush I wan...
I'm not particularly knowledgeable about spreadsheet wrangling, so I could use some assitance with a seemingly simple problem. I have a column of year values which I am sorting by. I'd like to find the quantity per year ( read: number of repeats of each year value ). I'd like to chart said values. I'm not sure how to make this happen. I...
I want to read the count of characters for a cell and set it to another cell. Could you please tell me what is the function "Formula" to do that?. Thanks ...