Formula To Select Value That Satisfies Conditions
Apr 3, 2009
Need a formula that will use column F as a reference to find out if there are 3 previous wins and show the value of FG (H column) and if not it should return just a character ("-" eg). For example in our case the end result should be what is shown in column J. The problem I have is that the sheet contains a break in stats after every 20 games and more conditions have to be added to accomodate this and have not been able to succesfully do it. You can use other columns (as shown) for references if it is easier. See column L for what I have come up with but it does not quite work as I wanted it - as it shows a false where it should return just "-" and also in the row after the last data entry it shows a 0 instead of "-". Table gets updated.
Any other solution (not using IF function) would be fine as long as I get the end result in column J (as shown).
View 10 Replies
ADVERTISEMENT
Mar 26, 2013
In a particular sheet the data (with formulae) are present from rows E5:Q5000. I require a macro which automatically does the following:
- When user enters “submitted” in the row Column N, entire rows above that cell should be selected, and copy-pasted as values to remove all formulas. So if user enters “submitted”, at N31, then data set E5:Q31 should be selected and copy pasted special so as to retain only values.
-Finally the copy pasted data should be sorted as per the status of Column N.
View 4 Replies
View Related
Apr 26, 2014
I have an excel sheet with lot of data.i want to use vlookup function for getting what cell i want. the same time it will also check if and only if two cells are matching then return the result.is it possible. for example:
101 3.2 7.3 200 500
102 5.2 8.3 200 450
103 3.2 7.3 200 750
101 7.3 7.3 200 450
102 7.3 7.3 200 500
103 7.3 7.3 200 600
if my lookup_value is 103(ie A3 ) in this table array and required column is 6th column(ie E6).
return the value if and only if B & C cells of the corresponding row matches. In the example B6=C6 then return the E6=600.
if i am using without this condition it returns the same row values ie E3=750 required is E6=600
View 12 Replies
View Related
Feb 13, 2014
I have a table with a lot of fields ( Date, Type, Name, Status ...etc). I want to make 2 textbuttons to put the Date intervals and a combo box to put the Type possibilities. After i put those condition i want to press a button to show only rows (from initial table)who are between data i entered and with kind of type i select.
View 1 Replies
View Related
Dec 11, 2008
I have 5 formatting conditions that I need to add in my macro and have just recently found out that I can't have more than 3 in my version of Excel - DOH.
Anyway, some research has indicated that you can use something called select case to get round this?
I need to colour the range as follows:
View 14 Replies
View Related
Feb 10, 2009
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something!
I have a spreadsheet where column O has five conditions:
1 PO in
2 High Probability
3 Medium Probability
4 Low Probability
Blank
I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA.
However, I'm having difficulty colouring the whole row; how do I do this?
e.g. what I need to do is:
If O2 = "1 PO in"; Select A2:T2; Colour green
In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
View 9 Replies
View Related
Dec 22, 2012
I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)
***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")
unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.
View 9 Replies
View Related
Jun 19, 2014
I am having trouble with formulas below, when i try to use AND, OR, i having trouble .
=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
View 8 Replies
View Related
Jun 17, 2009
I'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0.
What i mean is if i have the following columns of data:
A B
6 2
2 1
4 0
A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.
View 3 Replies
View Related
May 22, 2014
I am tracking SLA with some severity 1, 2 and 3.
Sample
There is 1 ticket raised on 23 May 2014 09:00 AM.
There are 4 severity with below condition (in hours)
Severity
SLA
1
1
2
24
3
720
4
2160
Based on condition above, so if task 1 get severity 1 then the target that should be on 23 May 2014 10:00 and so on
Desc
Severity
Date raised
time raised
target date
target time
if severity 1
1
23-May-2014
09:00
23-May-2014
10:00
if severity 2
2
23-May-2014
09:00
24-May-2014
09:00
if severity 3
23-May-2014
09:00
22-Jun-2014
09:00
View 9 Replies
View Related
Jul 29, 2014
I'm looking for a formula that links different conditions together.
For example, I'm entering the formula into G9 and I want the formula to be if G7 is equal or greater to G16 then the answer should be 30, if not then the answer is 0.
Simple enough I know this is my formula to use "=IF(G7>=G16,30,0)" but I want it to look past that.
Basically I want it to say if G7 is equal or greater to G16 then the answer is 30, if not it's 0 BUT if G7 is equal to greater than G17 my answer is 60 BUT if my answer is equal to or greater than G18 my answer is 120.
G17 will always be a larger number than G16, and G18 will always be a larger number than G17.
View 3 Replies
View Related
Nov 29, 2013
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
View 4 Replies
View Related
Dec 22, 2006
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:
= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))
But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
View 9 Replies
View Related
Feb 18, 2013
"IF" formula. I want this in one cell but not able to do so :
If A1180, then Over Utilized, If A1=180, then Fully Utilized.
If the formula returns 0,1,2 instead of Under Utilized,Over Utilized, Fully Utilized respectively, I have no problems.
View 1 Replies
View Related
May 11, 2006
I need a formula for:
If nothing is entered into J1, leave it blank
But if J1 is greater than or less than H1, have the text "why?" appear in the cell. Late in the day and getting a lil brain dead.
View 4 Replies
View Related
May 14, 2014
1) Input data are static and helper columns can be added if needed.
2) Filter will be dynamic range (in attachement is the filter static), and the count of years can be changed on users request. So there could be only 2011, but also 2011+2012, 2012+2013+2014 etc.
3) In col 'J', the is what I know to do, but I do not want to use SUMIF+SUMIF+SUMIF... for each year (the count of years will change througt time as mentioned above).
View 3 Replies
View Related
May 23, 2014
Combine two lookup/IF formulas:
[Code]......
So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.
View 5 Replies
View Related
Jan 6, 2014
Current simplified situation:
Department A
Employee_1
Employee_2
Employee_3
(empty cell)
Department B
Employee_4
Employee_5
Department C
Employee_6
Employee_7
Employee_8
Employee_9
Employee_10
With the VLOOKUP function, I have looked up the row numbers of the departments. Subsequently, I'd like to find the last employee of that department, based on the department's name and/or the department's row number.
A department-typed cell is non-empty and never contains an underscore. An employee-typed cell is non-empty and it always contains an underscore.
The simple and non-sufficing formula is the VLOOKUP of the row number of next department minus one. But with adding, moving and subtracting departments rapidly, this is not an option.
View 4 Replies
View Related
Mar 5, 2014
I need a formula that will determine if a line is active or expired by first looking at the category and then looking at the number of days. (While still incorporating the ISBLANK section of the current formula.)
For Example: If the category is either FWW or FWW Ext then the line will expire after 365 days. If not, then it will expire after 180 days.
I've attached a sample spreadsheet.
Book2.xlsx
View 12 Replies
View Related
Nov 6, 2008
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
View 2 Replies
View Related
Feb 8, 2009
I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.
Like this one, which counts rows where conditions are met in four columns:
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5))
and this one, which sums the values of the same
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))
How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?
Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).
I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.
View 2 Replies
View Related
Apr 23, 2009
i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need.
Basically i need to say the following:
=if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
View 3 Replies
View Related
May 17, 2009
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View 5 Replies
View Related
Mar 7, 2012
I am trying to write a Sumif formula based on two conditions. I want to sum the data in column AK if the data in column D = BUD12 and the data in column E = US Custom. I have written the following formula but am getting a #VALUE error.
=SUM(IF($D$25:$D$700="BUD12",IF($E$25:$E$700="US Custom",$AK$25:$AK$700,0),0))
View 6 Replies
View Related
Apr 28, 2013
I would like to find out, how would look formula in excel 2003 to perform process like discribe below:
(in cell K1 formula)
=sum (A1+B1) then if sum of A1 and B1 is lower then 100 then add C1 but if sum of A1 and B1 is higher then 100 then subtract 100 and add C1.
View 9 Replies
View Related
Dec 6, 2009
Can a sumproduct formula accomodate multiple criteria?
The following is a sumproduct formula, for just one condition.
SUMPRODUCT(--(A1:A100="Red Sox"),--(B1:B100""))
View 9 Replies
View Related
Jan 16, 2010
I have a spreadsheet that contains two worksheets called: MasterApplication and LocalAdmin. The Local Admin worksheet as shown below contains the following information starting on row A1:D456 ....
View 9 Replies
View Related
May 16, 2007
Please take a look at this sample spreadsheet.
[url]
I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
View 3 Replies
View Related
Feb 20, 2014
I work for a farm where we receive fresh produce from harvesters daily. I created a workbook with worksheets(ws) for my daily inventory counts according to product type and separate worksheets for each day of the week showing the orders shipping for that particular day.
I collaborated the data from the inventory count ws and the ws for each day into one ws called Harvest. I am trying to figure out how to keep a running total of what needs to be harvested vs. what is in inventory so that I can show my harvesters what new product is needed to fill orders for today forward, after subtracting what is available in inventory, and showing the amount shipped for past days . Another part that's throwing me is that harvest brings in new product daily and sometimes they bring in a surplus.
Here is an example:
Lets say today is Tuesday and my Inventory ws shows we have 50 kale in inventory - cell D10
Monday ws shows an order for 50 kale, cell D14
Tuesday ws shows an order for 100 kale, cell D14
Wednesday ws shows an order for 100 kale, cell D14
Thursday ws shows an order for 100 kale, cell D14
On my Harvest ws, Since today is Tuesday, how would I keep the Monday cell showing 50 kale as the amount shipped on Monday, Tuesday cell showing we need 50 kale to be harvested to add to the 50 in inventory and fill the order of 100, Wednesday cell showing that we need 100 kale harvested, and Thursday showing that we need 100 kale harvested?
If Harvest brings in a surplus of 100 kale on Tuesday so we have 100 kale in inventory on Wednesday how do I get the Harvest ws to now show that Monday cell should still show 50 kale shipped, Tuesday cell should show 100 kale shipped, Wednesday cell should show that we don't need any kale harvested since we are using the 100 surplus in inventory, Thursday cell should show we need 100 kale harvested.
View 14 Replies
View Related
Jan 1, 2009
I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.
View 5 Replies
View Related
