I am trying to average two cell references for the row component. I am unsure if INDIRECT is the correct function?
In cell A1 it displays the row number of the start of the range to average eg "49". Then in cell A2 it displays the row number of the end of the range eg. "107".
I need a way to change the row references in cell A1 and A2 easily and to change all subsequent formula.
So if I want to average the Column C, what would the formula be for the cell to display the result =average(C49:C107) but incorporating the references in A1 and A2?
Each sheet contains the test data for an individual class and I am trying to create a "Totals" sheet where I can show the average that each class had on a particular test and the letter grade breakdown. I am trying to create one 'average' formula that I may then copy across that will change the sheet reference automatically without me having to manually re-create the formula every time I add a new class sheet. I tried using the INDIRECT function but could not get it to work. Keep getting #Ref error.
how to make the average,median,max, and/or min functions ignore cells in the referenced range that contain the #value! error? All four of the functions are returning #value! because one or more in the referenced range have the error.
i need to write a formula that spits out the average of a series of numbers when (if):
every time "CAD" appears (column A) it takes the numerical figure of the cell directly to the right of it (column B). the data is arrange vertically, e.g., the range of "CAD"s is all vertical (column A), about 200 entries.
is there a formula/function that takes data from the columns completely adjacent to one another?
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code: =COUNTA(ABC!A:A) What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB ABC CDE
And have a formula in column B that converts this to
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I have 2 problems relating to LOOKUP. Not sure if Excel can perform these calculations as they could get to complex.
Problem 1 Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes) AB Bob1 Jon2 Fred3
Tab 2 (Dynamic, changes each week) AB Jon Fred Bob
So it would work as follows. Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week Example.
Week1 1Jon 2Bob 3Fred
Week2 1FredUp 2 2BobNot Moved 3JonDown 2
Can Excel calculate/show the actual movements of league positions?
what formula can i use to take the average daily excahange rate (and i have a list set up on worksheet) and make it an average weekly exchange rate? So that the formula recognizes data for each week (the weekly data is on a separate worksheet). attached is an example of the data... trying to go from daily average to weekly average...
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.
i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?
I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.
Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don't even know how to change to that style on my Mac.
I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1. After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
I'm basically trying to create a gradebook for a class in which there are five categories of assignments, and each category is worth 20% of the final grade. I need to be able to calculate subtotals for each of the five categories as well as a final grade for the course. I have set up my workbook so that a code appears next to each grade that is entered. That code corresponds to the category as follows:
p = participation o = organization s = selfwork g = OGT project e = final exam
So I need a formula that will sum the values of any cells that have a "p" in the cell to the left, or any cells that have an "o" in the cell to the left, etc. I thought an array formula would work but apparently not if my range is made up of noncontiguous cells.
Here's how things look right now:Screen Shot 2014-06-26 at 10.21.36 PM.png
So, for example, I need a formula that will find that Kylie has earned 10 points on the OGT project (the green category).
What I want to do is enter a date in cell F2 and it will look up that value in column c and will then add up that days number and the 2 days numbers before that date in column d and average them out.
So in the example i have used 20 january 2014, the 20th ,19th and 18th of january have values of 100,40 and 10 so in cell I2 it should return the value 50 ( the average of the 3 numbers )
But this would change with new dates going in cell F2.
I need to get the average of every 6th cell in a column, excluding errors --- three times.
The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8.
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e. 11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
