I'm trying to calculate the conditional median of a chart that looks like this: A | B ------- x | 1 x | 1 x | 3 x | y | 4 z | 5 I'm using MS Excel 2007. I am aware of the AVERAGEIF() statement, but there is no equivalent for Median. The main trick is that there are rows with no data - such as the 4th "a" above. In th...
I have two columns but the codition I would like is to be evaluated from one cell to another. The first column has cells which have a drop down validation with names, and the second will activate only if a certain name from the adjacent cell is selected. so far i only found this code but it does not seem to work: Private Sub Worksheet...
I have a spreadsheet in which I'm suppose to fix up broken formulas. Does anyone know what this is 'meant' to do? =#REF!A33 ...
I have two columns, the first column will have the name of a object, the second is who it belongs to. I want a new sheet for each person to list what they had assigned to them. here is a example: dog F cat F bell S whistle bird F So Fred has a dog, cat, and a bird; Scott has a bell; and no one has a whistle on their page. Now doing a...
If I have a block of cells such as A1:D5, what's the quickest way of multiping each cell by another number, 1.1? ...
I have an excel sheet (see image below) Here the requirement is to get the sum total of all the digits in the cells in column 'C' and put the value in the corresponding cell in column 'B' (in my example - 1 + 0.25 + 0.25 + 1 = 2.5). The values in the Column 'C' could change dynamically based on the inputs given by the team. The require...
I am creating a spreadsheet which has a column of data, and I would like to calculate the varaince of x number of rows based on x inputed. Any advice? ...
In the following Excel spreadsheet, I need to make the following calculations: Input Color Selected Output ------- -------------- -------- 40 red 40x18 40 blue 40x12 40 green 40x16 40 yellow 40x13 39 ...
I am attempting the following formula: =concatenate(A1, " ", B1, " - ", C1) Where column A is text, B and C are dates. The concatenate function returns the numerical value of the date, rather than the text value. How do I fix this? ...
Given a list of workbook's filenames, write a formula to reference a specific cell value in the specified workbook. A----------B-------C-----D--------------------------------E---------- workbook1 sheet1 A1 '[workbook1.xlsx]sheet1'!$A$1 =(???? workbook2 sheet1 A1 '[workbook2.xlsx]sheet1'!$A$1 workbook3 sheet1 A1 '[wor...
Is it possible to set a rows height in a formula in Excel? For Microsoft Office 2003. ...
I need a formula to return the value of Data for the last match of "Text". Row number is also acceptable. Macro is NOT acceptable. Name column is unsorted and cannot be sorted! Only column "Name" is used as lookup value. I would rather use a/multiple helper column(s) instead of an array formula. Row Name Data 1 Joe 10 2 Tom 20 3 ...
hi for example i have to write this in my formula Sheet1!A1:HM232 is there any other way of writing this (for example using only numbers, without any letters) Sheet1!Cells[1,1]:Cells[232,221] thanks in advance! ...
hi, i have a c# code which gets the (excel-2007) worksheets used range as follows Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.GetActiveWorksheet(); Excel.Range range = (Excel.Range)ws.UsedRange; for the current worksheet i know exactly that range is A1:HM232,...but in the future that range may change... how is it possib...
I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g. string words = "some text".split(' '); The problem is that I can't be sure that there is more than one word in the cell. If I try to use the FIND() or SEARCH() functions, they return #VALUE if there is not space. Is there a...
i have tons of data that i would like to highlight only those cell that fulfill some criteria, said value < 0.100. Please advice. ...
hi i have a c# program which gives the excel2007 range, its formulaarray as follows Excel.Worksheet ws_res = (Excel.Worksheet) wb.Worksheets.Add(mis, mis, mis, mis); Excel.Range range_res = (Excel.Range)ws_res.get_Range("A1","HM232"); range_res.FormulaArray = "=(IF((IF(Sheet4!A1:HM232=1,0,"+ ...
hi, can anyone tell me why this does not work Excel.Worksheet ws_res = (Excel.Worksheet) wb.Worksheets.Add(mis, mis, mis, mis); Excel.Range range_res = (Excel.Range)ws_res.get_Range("A1","HM232"); range_res.FormulaArray = "=ROUND((IF((IF(Sheet4!A1:HM232=1,0,"+ "IF(Sheet4!A1:HM232=0,1,Sheet4!A...
hi why do i get the runtime error 13: type mismatch error while running the following code Application.Goto Reference:="R1C1:R232C221" Selection.FormulaArray = "=ROUND(a(),0)" Selection.Replace What:="a()", Replacement:="IF(IF(Sheet4!A1:HM232+Sheet5!A1:HM232=2,0," & _ "Sheet4!A1:HM232+Sheet5!A1:HM232)+IF(Sheet4!A1:HM232+Sheet5!A1:HM2...
On the Daily Dose of Excel website, written by the late Frank Kabel, there are some formulae which can stand in for ATP functions. Not being an Excel guru, I'm struggling with converting one (so far!) to VB6. (Why I'm doing this I may relate once the NDA runs out.) The problem I'm having is with the code standing in for SERIESSUM, namel...