I'm using a "Large" formula with a variable for the number of cells to sum and then later divide by. However, I want the Large formula to look at the X largest values from a column three to the right, then sum the values in the original column. This wouldn't necessarily be the X largest cells in that original column. I believe this would be some sort of offset, but I'm not very good with those.
View 3 RepliesI have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab.
Question #1 how this formula works or what it does.
Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.
i have this formula below which works but if the criteria is not there it says FALSE because of the B22:C45,2,0) bit i would rather it says something like "no dissertation" is that possible? ...
View 10 Replies View RelatedI'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.
In cell G1 I have my snapshot dates header and dates running from G2 to G31. Next to in Column H I have the sold items by Product A.
There are 6 different snapshot dates and I need them to be horizontal with a formula and none duplicated with the largest (newest) date first so I can SUMIF the sold item per date. If I simply pull a LARGE formula it will return 24-05-2014 in all rows is it is the biggest "value".
When I them add new snapshots (biweekly) then the horizontal snapshot line will automatically add the newest dates (i.e. find the largest or newest, the 2nd newest snapshot, the 3rd newest, etc. Hence, in K2 we would have 09-03-2014 with 672 sold items in K3 (via SUMIF), in L3 we would have 18-03-2014 and 7523, in M3 23-03-2014 and 1703, etc.
I'm not sure if I explained it well enough. I'm attaching a file if this makes more sense.
G1 G2
Snapshot DateProduct A - Sold Items
09-03-201423
09-03-2014354
09-03-201435
09-03-2014254
09-03-20146
[Code] .......
Latest Snapshot
09-03-201418-03-201423-03-201410-04-201424-04-201424-05-2014
6727523170310787201035
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
I am trying to create a formula to determine if my students made a point in high standers, which means if they got a level 3 and about in 2009 verse the student's 2008. Now if they stayed on the same level did they make learning gains. So what I need to do is look at the grade level of the student (Colum C), then 2009 Math Developmental Score (Colum J), and Finally 2008 Math Developmental Score (Colum M). So if the student scored a level 2 in 2008 and a level 3 in 2009 he gets a point, but if he stayed on the same level. The formula needs to look see if he made learning gains. So if the student is in grade 4 and was a level 3 in 2008 and 2009 he would need to score 163 points higher in order to get a point. I have created a helper table with the information need to calculate the information. I have been working on this for about a week I can’t seem to get it right.
View 3 Replies View RelatedI am using the following code to sum a range of cells in a column and compare the result to another column, bolding that column if the two values are not equal. This works, but I am sure someone can offer me a far more elegant (and perhaps faster?) solution.
Dim rngRange As Range
Dim c As Range
Set rngRange = ActiveSheet.Range("F22:F522")
For Each c In rngRange
If c.Value c.Offset(0, 1) + c.Offset(0, 2) + c.Offset(0, 3) + c.Offset(0, 4) + c.Offset(0, 5) + c.Offset(0, 6) + c.Offset(0, 7) + c.Offset(0, 8) + c.Offset(0, 9) + c.Offset(0, 10) Then
c.Font.Bold = True
Else
c.Font.Bold = False
End If
Next c
I saw a formula on another website that shows calculating a YTD formula using the offset function. The address is:
http://www.beyondtechnology.com/tips010.shtml
I was working on a version where you enter the months in row 1, number data in row 2, and have the month to use in the formula in cell N2 but it did not work. Any suggestions on how to do it properly? Here is my formula:
=SUM(OFFSET(A2,0,0,1,MONTH(N2())))
I have this formula (part of a block covering 4 columns) in cell D12.
D11*AVERAGE(DATA_2006!$A$14:DATA_2006!A14)
Every month this bock of information has to be copied across 4 columns i.e.
D:G is copied to H:K then H:K to L:O and so on.
When I do this the calculation needs to change as follows -
Month 1 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!B14)
Month 2 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!C14)
Month 3 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!D14)
Because I am copying across 4 columns this happens
Month 1 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!F14)
Month 2 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!J14)
Month 3 D11*AVERAGE(DATA_2006!$A$14:DATA_2006!N14)
I have named a range to find the last cell in a column . I then want to build a table which picks this cell and the cells to the right and above (its for a 6 month rolling table).
Ive attached spreasdsheet so i hope someone has a clue what im trying to do.
I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.
I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).
If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:
=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)
I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
Currently have a formula to enter the last value in a row in cell B1. What i need is the formula below in B1 then a formula in A1 which will show the value before the last entry in row 1. Maybe an offset unless there is an easier way
=INDEX(1:1,MAX(IF(D1:Z10,COLUMN(D1:Z))))
i.e.If H1 was the last entry in the row I want a formula for H1 in cell B1 and a formula for G1 in A1
I would like to have a sumproduct formula to sum up in a paticular table in my worksheet. But its in a weird table format. Look in cell A1 that is my criteria it should sum cells G24 thru G28 and it should return the total of 1.8000. The rows could varies from time to time in those table formats. Does anyone knows how to accomplishment this.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG27=
ABCDEFG1114Y1.8000 2 114C 3 Material #DescriptionMan/Machine ScrapMaterial 4 Operation # Std/Rate lbs5 100510 6 101010 0.56207 8 9 10 114D 11 Material #DescriptionMan/Machine ScrapMaterial 12 Operation # Std/Rate lbs13 14 S-SO2 0.400015 100510 0.895016 101010 0.200017 18 19 20 114Y 21 Material #DescriptionMan/Machine ScrapMaterial 22 Operation # Std/Rate lbs23 24 100910 25 670114X 1.000026 670114C 0.400027 670114D 0.4000Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am using an offset match formula to look at a named range of data and create a validation list of property address's based on certain criteria in cell d1.
My question is it appears the information which is status information seems to have to be sorted, otherwise the list doesnt work properly.
is there another formula to accomplish this with out having to sort the criteria in cell d1 ??
This is the formula i am curently using, it works great if "ListStatus" is sorted and not if unsorted.
=OFFSET(AddressDisplay,MATCH($D$2,ListStatus,0)-1,0,COUNTIF(ListStatus,$D$2))
I've noticed something strange when trying to solve this problem (XL07):
ABC1Data1Data2Sum21
32
43x654
65x976
87
98x21109x9
I was trying to create the sums in column C from "x" to "x". So I built the following array-formula in C2 and downwards:
{=SUM(OFFSET(A2,,,MAX((B$1:B1"")*ROW($1:1))-ROW()))}
It works fine, but unfortunately it also shows the "subtotals" in the rows without an "x". "No problem", I thought: this can be easily done with an IF-function surrounding the formula, like this:
{=IF(B2="","",SUM(OFFSET(A2,,,MAX((B$1:B1"")*ROW($1:1))-ROW())))}
But what happened? I got "#N/A!" in each "x"-row!!!
How can it be, that the IF-function affects the inner function???
Meanwhile I've found another formula to solve the original problem
I found the following formula, with different sheet and cell references, on someone's site: =OFFSET(CurvCalc!$E$12,MATCH(MAX(CurvCalc!$E$12:$E$10000)+1,CurvCalc!$E$12:$E$10000,1)-1,0)
I googled for the last couple of days but can't find this site again. I have found several that come close like Dave's at http://www.ozgrid.com/News/excel-dynamic-ranges-vba.htm
http://www.cpearson.com/excel/lookups.htm
http://www.cpearson.com/excel/lists.htm
and Microsoft's at
http://support.microsoft.com/default...;en-us;Q152407
I am using the OFFSET formula (=OFFSET($A$1,0,0,$B$1,1) and instructions (http://www.ozgrid.com/Excel/DynamicRanges.htm) I found on this site (which is great, by the way).
In the instructions on the site, it requires you to define a range for which the formula to apply. What I tried to do was skip naming the range, and simply using the OFFSET in various cells throughout the file. However, whenever I enter in a row [height] of greater than 1, the formula returns a #Value error. I want to use this offset on multiple data ranges, so naming all of them would be rather time consuming. Is there any way to skip this step?
I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.
View 4 Replies View RelatedI've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A
B
C
D
E
1
DATE
TEAM
PITCHERS
RESULT
2
4/1
nyy
Sabathia
???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
Here is my formula:
=IFERROR(INDEX(Stored!$D$22:$AN$795, MATCH(D11,Stored!$E$22:$E$795,0), MATCH(E10,Stored!$C$22:$AN$22,0)),0)
For some reason it keeps pulling back the value that is one cell to the right, so one column off.
What could it be.
In this example it should pull back N363 and it is pulling back O363.
The following formula works very well for me but it will only return a number and not text.
=SUMIF(B4,K4,OFFSET(B4,0,Q4,))
I have a problem with a cumulative countif and offset formula that I am unable to solve. See the attached spreadsheet for an example of the dataset I get out from our systems.
I want the formula to be fully automatic, so when I change the month in column K2, it will automatically count all the different outlets in column B based on criteria in column L3 and downwards. The dataset will be updated monthly obviously.
I believe that it could be solved with the usage of a countifs + offset formula or something similar.
The main idea of the spreadsheet is to track the movement of customers between different chains. It is thus important that customers that show up in January will not be counted again in February or March, thus creating an unique countif list based on a given month.
Number of outlets example.xlsx‎
Is there a way to make the last two parts ("height" and "width") of the OFFSET statement formula static (meaning they alway refer to the same cell)? I am inputting a different number in the "colum" section of the formula and every time I do...it changes which cells the "height" and "width" refer to.
View 6 Replies View RelatedOn the attached spreadsheet, i am trying lookup the selection name in column A on the summary sheet and search through column A of the other three worksheets adding together the three answers but something isn't working as the end answer is incorrect. I have tried vlookup and also tried combining offset/match but neither way i am getting the correct answer. I removed a lot of the data to simplify things and changed the figures to fictional ones for privacy reasons.
View 2 Replies View Relatedi'm working on an excel file that consists of 31 sheets one for each day of the week, the information about how much we make each day is inputted on the end of each day, i've consolidated all the sheets into 1 big sheet and now i want to create charts over the production, so what i would like is a code that searches the huge sheet for Machine name (Found in Column A and Column I) then walk 5 cells to the right and find the number of litre's made by that machine on that shift.
The different shifts are
Formiddagsskift - Morningshift
Ettermiddagskift - Eveningshift
Nattskift - Nightshift
I will include an example of the data. The data should be pasted to the "Formler" spreadsheet. It doesn't matter if you don't wanna do this i can simply do it later i just need the code to search, and go 5 cells to the right and remember which cell it is, and make sure it is in the right "Shift" ie, all the 282 machines for morningshift are summed in c7 and c10 has eveningshift and c13 has nightshift.
Quite time consuming to search through 31 days of production to find the correct machines on the correct shifts. And the data changes for each month so this would be a great asset to me.
I'm looking for a formula that will take the cells used as a heading and place them cell into columns with the corresponding rows under the heading.
I'm thinking that the formula will have an offset that counts the blank cells in column A then place the text from the heading cells (by heading cells I mean the ones that say "Type A" and "Code X") next to the rows with the products.
Maybe a VBA script? What do you think?
Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows
HTML Code:
=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))
The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).