Array Formula To Find Offset Value Based On Certain Condition?
May 8, 2014
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
View 6 Replies
ADVERTISEMENT
Jul 27, 2009
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
View 9 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
May 21, 2014
Add a condition to this formula Let us assume for the column (classes) and the condition is selected from the cell "G1"
Condition E2: E30 = G1
ID
NAME
CODE
Birthday
[Code]....
View 3 Replies
View Related
Jan 7, 2009
I am running an array formula which is working fine except that I now need to add a further condition: that a one of a number of values in cells C1:C8 is found in range $a1:$A500. I've tried Or with comma separation and with * separations but nothing seems to work.
Can anyone advise me of the syntax?
View 9 Replies
View Related
Nov 13, 2011
with the formula below..
=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)
*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475)
View 8 Replies
View Related
Jan 11, 2010
i'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.
View 13 Replies
View Related
Aug 21, 2007
I have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:
in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing
Set target = Sheets("Sheet1"). Range("A1:A9000")
For Each cell In target
If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"
Next cell
A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09
and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination
View 4 Replies
View Related
Aug 5, 2014
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
View 8 Replies
View Related
May 24, 2012
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?
View 7 Replies
View Related
Aug 6, 2014
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies
View Related
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
View 9 Replies
View Related
Aug 18, 2014
In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".
I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.
View 4 Replies
View Related
Mar 22, 2009
I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is
< 50 is negative
= 50 is neutral
> 50 is positive
For example
Column A Column B
45 Negative
50 Neutral
80 Positive
View 4 Replies
View Related
Apr 18, 2008
I'm trying to hard-set the value of the current cell (i.e. remove formula and keep value) if there is data entered into another cell. =IF(C14="",IF($M$5="",G13*($I$6+1),G13+($L$6/ COUNTIF(A$12:A$200,"<"&$M$5))),Set the value of this cell and remove the formula.
View 6 Replies
View Related
Nov 10, 2008
I have 13 columns (C-O) of data that will span up to 1000+ rows. I want to put a single row of cells at the top of the sheet that will display the last row of data from all 13 columns. I want the row at the top to always represent the last row of data, which will always be the most current data.
This is what my data will look like: ....
View 9 Replies
View Related
Jun 29, 2009
(Thread: Max array with multiple columns) I got the following array formula:
{=MAX((A45:A47=A28)*(B45:D47))}
Here, where the value in A45:A47 is equal to A28, the maximum value is pulled from the same row, within cells B45:D47.
e.g.
A28 = Apples
A45 = Pears
A46 = Apples
A47 = Bananas
B46 = 10
C46 = 20
D46 = 23
The formula returns 23.
This is great. However, in order to control for a potential situation that will arise, I need to adjust this to find the first matching value within the data set. I do not know what the MAX should change to in order to find the first matching value.
Using the above example, if the data was now:
A45 = Apples
A46 = Pears
A47 = Apples
B46 = 10
C46 = 20
D46 = 23
How do you get the formula to return 10?
The position of "Apples" will change, hence the need for an array formula.
View 9 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Feb 22, 2013
I am trying to find a value in an array based on partial input from a user.
The user will enter the last four digits of a number.
Then the formula should find the number in a list and return that complete number
I will explain using example.
Here is the list of numbers (array):
3003280197
3003283233
3003625456
User input= 3233
formula output = 3003283233
View 3 Replies
View Related
Mar 17, 2013
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
View 8 Replies
View Related
Jul 10, 2006
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View 5 Replies
View Related
Feb 13, 2014
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
View 1 Replies
View Related
Jun 3, 2014
=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
View 8 Replies
View Related
Jul 20, 2006
I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.
View 4 Replies
View Related
Jul 18, 2012
I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
The formula I am currently using is:
=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))
Sheet 1 (Named: Main)
Number
Date
Formula
1
15/06/2012
[Code] ....
Sheet 2 (Named: Trees)
Number
Date
1
05/06/2012
[Code] .....
View 2 Replies
View Related
Dec 19, 2013
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
View 4 Replies
View Related
Mar 4, 2014
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
Here is my workbook: Book1.xlsx
View 4 Replies
View Related
Feb 11, 2014
I have 7 sheets including the table as you can see on the attached. Now, each sheet is allocated to individual items e.g. item 10, item 20 and so on. I can't figure out how to return all the Item 10, 20, 30 ... to the corresponding sheet. It says Item 40 to all sheets.
View 8 Replies
View Related
Sep 23, 2007
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View 9 Replies
View Related
Jul 19, 2013
I currently have a Row with this value
row1 lh lh lh
row2 8 12 8 5 2
First I will need to check if the column has an "lh" if true then sum row2, however i will need a second condition that will check that if >8 it will use the value 8 instead of 12.
However if value is<=8 get that value from the cell.
In this case the correct answer is
8 + 8 + 5 = 21
8 (because it is <=8 get the value from the cell) + 8 (because 12 is>8 use the value "8") + 5 (because <=8)
View 3 Replies
View Related
