Find Max With Condition
Apr 4, 2007
I need to look up a maximum value in a table depending on a condition. I have attached the spreadsheet. Basically, I need to find the maximum value for " Sum" column on sheet "Column Data" depending on the Coltype value on Column Summary. This should be returned to Sheet "Column summary"
For example, the formula should look in Cell A4 of column summary, see that Coltype is B, then look in column C of sheet column data and find the maximum value in column F:F of all the Coltype (column C:C) values that are of type B.
View 3 Replies
ADVERTISEMENT
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
Feb 1, 2010
The fuction works if the statement is found to be "True" but returns a value error
if the statement is "False". Anyone know the reason I'm getting a value error?
View 5 Replies
View Related
Mar 14, 2014
Let's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
View 4 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
Aug 4, 2014
I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?
View 5 Replies
View Related
Jun 6, 2012
I want to find the most recent date in column D, when the variables in column E are all the same. There are many different variables in column E, and for each group of the same variables (eg. all cells containing "A"), I want to find which is most recently dated (dates are in column D). This is my code so far...
Sub adddiv()
Dim ticker As Variant
Dim freq As Long
Dim csheet As Worksheet
Set csheet = Worksheets("Sheet2")
[Code] ....
View 3 Replies
View Related
Dec 5, 2006
I want to search a column, from A30 moving up to A10 for the first value in excess of 0.
Having found the value the search stops and I want to select the cell next to it in B column.
Then I want to check if a value in B column exists. If no value exists I want to enter a fixed value from another sheet.
If a value does exist I want to take no action and move on to Column C to repeat the checking process for columns C and D.
Then I want to move to the next spreadsheet and repeat the process.
The code I have written so far is below and I just can't get it to work.
I have attached a spreadsheet to illustrate the problem.
Sub Closingdata()
Dim a As Integer
Dim b As Integer
Dim rngOutput As Range
Dim shtTemp As Worksheet
Dim vntName As Variant
For Each vntName In Array("sheet1", "sheet2")
Set shtTemp = Worksheets(vntName)
shtTemp.Select
shtTemp.Range("a30").Select
Do Until ActiveCell.Value > 0
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
End If
View 7 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
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
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
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
Mar 24, 2014
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
View 2 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Aug 24, 2007
I recorded a macro to search for a cell with a specific string, and then modify that string. It works fine, unless there is no cell containing that string. If the spreadsheet does not contain the string, this error results:
Runtime error '91'
Object variable or with block variable not set
I need to modify the code to simply end the macro if the string does not exist, and although I'm sure it's really simple, I don't know how to do that.
Here is the code (minus initial comments):
View 9 Replies
View Related
Feb 29, 2008
I'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
View 9 Replies
View Related
Apr 12, 2014
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
View 9 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
May 23, 2006
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
Feb 20, 2007
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
View 4 Replies
View Related
Aug 5, 2009
DSee the attached.
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.
BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).
I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.
View 5 Replies
View Related
Jun 28, 2009
I have a rows of the following.
=IF(H1="","",IF(H1="Fail","1",IF(H1="Pass","2","3")))
=IF(H2="","",IF(H2="Fail","1",IF(H2="Pass","2","3")))
=IF(H3="","",IF(H3="Fail","1",IF(H3="Pass","2","3")))
=IF(H4="","",IF(H4="Fail","1",IF(H4="Pass","2","3")))
=IF(H5="","",IF(H5="Fail","1",IF(H5="Pass","2","3")))
The following are the outputs.
1
2
2
2
1
When I do summation on the output cells, the results returned 0. Do I have to do anything procedure in order to add up values that are generated by If-Else?
View 4 Replies
View Related
Feb 10, 2010
3 different condition using IF. See the attachment.
View 2 Replies
View Related
Oct 23, 2008
I need to find the last date when a company bought any product from my company.
So i have taken the export from the CRM, now i want if the company name collumn is same take the max function in the date collumn fro that perticular company.
I am attaching the file also with a small example.
View 10 Replies
View Related
Dec 11, 2005
Is thre an easy way to set up the following testing conditions:
- if A1=A2=A3= ... =A10, do something?
--
Additional information:
- I'm using Office XP
- I'm using Windows XP
View 9 Replies
View Related
Nov 12, 2009
I have 3 columns. Column A contains numbers, column B names and i would need columns C to contains the sum of the numbers of column A if they have the same name in column b. I have made an example to make it easier to understand so here is a printscreen of what column C would need to look like: http://img441.imageshack.us/img441/3417/57644131.jpg
View 5 Replies
View Related
Apr 16, 2009
Been teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...
Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.
Code so far:
Dim Cval As Integer
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, Cval).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
With Cells
.Font.ColorIndex = 10
.Font.Bold = True
End With
End If
Next i
End Sub
I can't work out the right sintax to get a if condition to work correctly.
I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...
View 9 Replies
View Related
Jan 22, 2007
Need VBA code to sum total all entries that are in range F7:H70 that are 600.00 or over into a variable called lng1099sTotal. For example:
......F....G.....H...
7..125..610..850
8..900..301..425
9..984..325..600
lng1099sTotal should equal 3,944.00
(610+850+900+984+600).
View 3 Replies
View Related
