Formula For Returning The Next Value Unless Value In Adjacent Column In 1
Feb 4, 2014
The first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.
Age Length Length+1
1 22 51
2 51 78
3 78 94
4 94 0
1 31 46
2 46 69
3 69 85
4 85 96
5 96 103
6 103 0
1 19 36
2 36 52
3 52 0
View 3 Replies
ADVERTISEMENT
Jun 6, 2014
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
View 6 Replies
View Related
Dec 20, 2013
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
View 9 Replies
View Related
Mar 5, 2014
I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.
If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)
Capture.JPG
View 6 Replies
View Related
Mar 14, 2014
How to do this or if there's even a formula or even a macro i could run to run this data.
I have three rows of data (29-31) from columns D to Q.
I would like to take D29 and add each of the values in the adjacent columns exactly once and sum up the value D thru Q
For example I'd like to return not just Sum(D29:Q29) but also Sum(D29:R29)+Q30, Sum (D29:R29) +Q31.
I guess that might be considered a 1X3 matrix & and 8X3 matrix, but I'm not really sure how to set up the return values for these equations nor how to return them very quickly.
View 1 Replies
View Related
Oct 7, 2009
I have a datatable which has a column containing up to 25 different text codes (which are currency pairs). I need to insert a specific number (as seen in the attached example) in a second column. This number depends on the text in the first column. I suspect this is very simple but at the moment, not for me!
View 3 Replies
View Related
Nov 18, 2011
I have three columns of data laid out like this:
Column A Column B Column C Column D
(blank) Purchase Orders Shipments # Shipment Reference Field
12345 1ZX123 xxOO12346
12346 1ZC345 KJH12347--45
12347 1Z7YYT 0000012345XXX
I'm trying to write a formula for column A that essentially looks at the field in column B, finds the instance of the data anywhere in the entirety of Column D, and when it locates it puts the corresponding field from Column C of that line in the results of column A. So the result above would turn out like this:
Column A Column B Column C Column D
(blank) Purchase Orders Shipments # ShipmentReferenceField
1Z7YYT 12345 1ZX123 xxOO12346
1ZX123 12346 1ZC345 KJH12347--45
1ZC345 12347 1Z7YYT 0000012345XXX
I have the origins of a formula here...but I don't know how to put in the part where i'm retrieving the data from Column C of the same line as the target data.
=INDEX($D$1:$D$100, MATCH(B1,1*LEFT($D$1:$D$100,LEN(B1)),0))
View 2 Replies
View Related
Feb 2, 2014
I have a spreadsheet with temperature data, all in one column (D), that was recorded every 10 minutes for several months. In two adjacent columns (E & F), I would like to record the daily max and min temperatures. I can manually do this with MAX and MIN formulas in column E and F respectively, MAX(D1:D144). But when I copy this formula to the next row, I get MAX(D2:D145) when what I really want is MAX(D145:D288)....and so on...
View 1 Replies
View Related
Apr 29, 2013
Frequency
Formula to show the final/total a numeric values appears in an adjacent column.
I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).
I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.
Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}
However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R
NB: My list is sorted by column C to ensure all numeric values are in ascending order.
Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final
View 5 Replies
View Related
Apr 13, 2009
I'm have a simple spread sheet that includes days and dates. I have a countif formula counting the individual days (as in: Monday = 5, Tuesdays = 2, etc...) I want a formula that returns the most frequent day in text format.
View 6 Replies
View Related
May 20, 2008
I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.
The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.
View 5 Replies
View Related
Apr 10, 2014
I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:
A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana
B1 - 3
B2 - 2
B3 - 1
B4 - 1
I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.
View 1 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
Feb 27, 2009
Im wondering if this can be done in excel..
2 columns
if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%
this is the way I was trying to do this...
=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)
I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
View 9 Replies
View Related
Sep 27, 2007
I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.
View 9 Replies
View Related
Jan 30, 2008
I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively.
What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
View 7 Replies
View Related
Feb 2, 2009
I need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.
View 2 Replies
View Related
Apr 22, 2014
In the B column i have dates. In the C column i have total hours.
Right now the following code counts how many instances of a date (Dt) are found and makes a decision if it is more than 3.
VB:
IVAL = Application.WorksheetFunction.CountIf(WS.Range("B:B"), Dt)
If IVAL > 3 Then
What i need is to SUM the hours (C column) for each instance of the date (Dt) in the B column, and test if it is greater than 24. so it would be similar to this:
VB:
IVAL = Application.WorksheetFunction.SumIf(WS.Range("C:C"), Dt)
If IVAL > 24 Then
Except that this code attempts to sum the hours of the C column and also wants to recognize the dates from the C column which would not work. I need something different than SUMIF. I need it to Sum the C column for the appropriate dates (Dt) in the B column.
View 2 Replies
View Related
Jun 20, 2008
I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet.
What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.
View 6 Replies
View Related
Feb 17, 2009
If I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies
View Related
Mar 22, 2007
since my previous post got a little complex I will make it easier so I can continue puzzling myself.
I have a value "10:00"
and a table:
00:00 08:00
08:01 12:00
12:01 14:00
etc
Now I sue LOOKUP("10:00",A1:B3). This will return "12:00"
But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work.
How do I look it up and then return either B, or 2 (in this example)
Once I have this I combine my 3 way lookup by combining my new found knowledge
View 9 Replies
View Related
Apr 6, 2007
I'm trying to return the # month in which a particular event occurred.
Example:
...1 2 3 4 5 6 7 8 91011 12 13 14 15 ....
...J .F M A M J J A S O N D J F M .....
1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0
2 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
3 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
5
.
.
.
.
I need a formula that will tell that for Item #1, Value 1 occurred in month 11, and #2 was month 8, etc. I would have thought that this is common, but I searched the postings for a long time. Maybe I'm over-complicating?
View 9 Replies
View Related
Apr 22, 2014
I am wanting to add the value of an adjacent cell and keep the SUM in another cell... depending on that cell value.
For example: H10 value is "JW" .. so that number adjacent (G10) would be added to the cell G4 (next to JW), and same with "NK". If it says "NK/JW" then I need it to be divided equally.
N528MW Revised.xlsx
View 3 Replies
View Related
Aug 14, 2013
It works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".
=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))
View 4 Replies
View Related
Dec 17, 2009
I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero.
When added using =a1+a2 the value returns correctly.
View 9 Replies
View Related
Mar 4, 2006
I have two columns with multiple sets of data, based on the entered value I
need the corresponding header returned.
For example:
Column A Column B
Apples Green
Red
Granny Smith
Mutsu
Red Delicious
Oranges Naval
Manderin
Clementine
Tangerine
Now based on a response in another sheet I need to return either "Apple" or
"Orange".
=INDEX('Fruit'!A1:A65535,MATCH(K50,'Fruit'!B:B65535,0)) will work if I copy
Apple next to each apple type, but for other items the list of options can
grow dramatically.
View 9 Replies
View Related
Dec 19, 2013
When I record a macro I use the paste down feature, but in the code it just sets the paste down to the last cell and sets that number in the code.
What do you put in the code so it finds the NEW bottom of the column when the columns get longer or shorter? I tried recording in relative reference and that did not do it - what's the trick?
View 2 Replies
View Related
Mar 5, 2014
I want to write some VBA code that simulates the double click of the square in the lower right of a selected cell. When you double click it, it fills every cell in column A until the first blank in column B. I was able to find this code searching around:
Code:
Selection.AutoFill Destination:=Range("A10:A" & Range("B10" & Rows.Count).End(xlUp).Row)
Selection.Copy
The issue with the above is that it copies the data all the way down to the LAST row in column B with data.
I want to have the macro stop when it finds the FIRST blank cell in column B.
Just as an example, the last row of data in column B is 100, but in row 67 the cell is blank, so I want it to stop at row 66.
View 2 Replies
View Related
Feb 21, 2008
I have a substantial schedule that has been completed by a 3rd party who has used the comment function for important data. Can anyone suggest how i can utilise a macro to extract all of the comments into the adjacent column?
The comments are currently against cells in column E and i'd like to extract them to blank cells in a newly inserted column F.
View 9 Replies
View Related
Jan 15, 2010
Sub For_Adam()
For x = 1 To Range("G7")
Calculate
Range("G11").Select
Selection.Copy
Range("B15").Select
ActiveCell.Offset(x, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next x
Application.CutCopyMode = False
End Sub
Now all I want is for this to be repeated in the next column, so everything is exactly the same but it doesn't start in B15, but starts in C15, then D15 etc.
View 9 Replies
View Related