Which Formula Should Use To Classify The Values
Jan 31, 2014
I would like to classify the chemical data into 16 groups based on their value. how to construct a formula to do the job?
My data look like this:
A B C D E F G
AsSbAgNiGroupcondition
0.10.50.080.6 14 1If As <0.1, Sb<0.1 , Ag<0.1 , Ni<0.1
2If As >=0.1, Sb<0.1 , Ag<0.1 , Ni<0.1
3If As <0.1, Sb>=0.1 , Ag<0.1 , Ni<0.1
4If As <0.1, Sb<0.1 , Ag>=0.1 , Ni<0.1
5If As <0.1, Sb<0.1 , Ag<0.1 , Ni>=0.1
6If As >=0.1, Sb>=0.1 , Ag<0.1 , Ni<0.1
[code]....
View 4 Replies
ADVERTISEMENT
Jan 27, 2014
I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.
View 7 Replies
View Related
Apr 30, 2014
I am trying to write a code that will classify based on criteria and color a cell. The code is below and it runs, but does not give the desired results that are in column K of the attached spreadsheet.
[Code] ....
TestBook.xlsm‎
View 2 Replies
View Related
Jan 20, 2014
The Data that I have is Grades:
Gade 1/Class A
Grade 2/Class B
Grade 3
Etc..
I wanted to use if function, but I couldn't as there's a limit on the characters that can be used in "IF Function", but unfortunately, I can't because I exceeded the limit, as I have too many grades to include in "Logical_Test",
What I am looking for if one of this data was found write 45 Days, if other grades that is not listed was found write 30 Days.
View 6 Replies
View Related
Jan 26, 2007
I need to classify a number into range
e.g.
D13 = 5101
if D13 range is 1 to 1000, write at D23 1000.
if D13 range is 1001 to 5000, write at D23 5000.
if D13 range is 5001 to 10000, write at D23 10000.
and so on until it reach 100,000
View 9 Replies
View Related
Aug 14, 2014
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
View 14 Replies
View Related
Dec 23, 2011
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
View 9 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Aug 26, 2009
Can anyone tell me why this formula is not working?
=SUMIF($D$6:$AL$6,"=TM Goals",D12:AL12)
The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.
=IF(E12="A",$L$5,"0")
View 12 Replies
View Related
Jun 3, 2009
i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......
in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.
View 4 Replies
View Related
Oct 5, 2011
I need to create a formula for below table. I need a sum 1 max values of each row. In below table i need a formula which will find 2 max values in given 4 values and make sum of it. But i need the header of the same instead of value.
In first example i need formula in E column which will find highest value and second highest value. like as 11 + 9 (Values are in column C and B) and i need result in column E as C&B
ABCD
79111
1250109
View 6 Replies
View Related
Jun 17, 2014
I have data that is set up like this:
A1 B1
Apple Sweet
Apple Juicy
Apple Worm ridden
Apple Round
Apple Cooking
Apple Grannies
Pear Green
[code]......
and would like to combine all of the duplicates from A1 into one field, with all of the options in B1 combined into one cell (separated by a |). So the above table would read:
Apple Sweet|Juicy|Worm ridden|Round|Cooking|Grannies
Pear Green|Tasty|Mouldy
Orange Tangy|Tasty
Peach Rare|Forbidden|Expensive|Squishy
View 5 Replies
View Related
Jun 27, 2014
Looking for a formula to extract values based on drop down options selected in column M3, N3 and O3. Desired outcome is in green color. See attached sample workbook for your convenience.
EastGolf2012, 2013
Month2012 Golf2013 Golf
January2849
February649
March6612
April
May
June
July
August
September
October
November
December
View 3 Replies
View Related
Apr 29, 2014
Bonus calculation model...
100% of sales target = 100% of bonus
110% of sales target = 125% of bonus
120% of sales target = 165% of bonus
etc...
How can I solve for someone who books 107% of sales target?
View 4 Replies
View Related
Oct 7, 2009
I am evaluating a wellness programme at work. One of the things we measure is BMI (Body Mass Index).
What I’m wanting to do is to categorise the BMI values into “flags”. For example someone who has a BMI of >35 could be red, someone with a BMI of 25-35 could be yellow, and <24 green.
What i’m wondering if excel can do is;
If I write the BMI value one cell can I get excel to automatically fill in another cell in either the colour or the name of the colour?
View 9 Replies
View Related
Oct 28, 2009
I am trying to calculate a value by using two lookup statements in one formula. However, I get either #N/A, or very wrong results. Here is the formula:
=LOOKUP($B26,GrapePricing!$A$2:$A$15,GrapePricing!$B$2:$B$15)*
LOOKUP($B26,$B$5:$B$17,$D$5:$D$17)
What I need is to get the value in B26 (it is text, "Merlot"), go to the GrapePriceing sheet and find Merlot in range A2:A15, get the price of Merlot from the GrapePricing sheet range B2:B15, then multiply that by the matching value in the D5:D17 range.
View 5 Replies
View Related
Jan 7, 2010
here's the situation and what i'm trying to do:
each row/record is as follows:
column A contains zip code
column B contains zip code population
column C contains city name
columns D contains state name
there are multiple duplicates in each column.
i am trying to calculate the population for each city and place that data in each row in column E.
in order to do so, i need to add the populations (column B) of all UNIQUE zip codes (column A) for each city (column B) / state (column D).
View 13 Replies
View Related
Feb 27, 2007
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies
View Related
Dec 28, 2011
I'm having trouble with a formula. Column A is a list of names. Row 1 is a list classes. What I need to be able to do is select a class from Row 1 in drop-down list in C14 and have it return the list of names in list starting at C17 based on the 'X'. In this example for SCIENCE it should return Tim Mark Jen. The actual file has 50+ names and 50+ classes.
Sheet1
ABCDEFGH1 MATHSCIENCEHISTORYLANGUAGEPEARTMUSIC2
JoeX XX X 3TimXX X X4MarkXX X 5SarahX X X6BrianX XX 7JenXX X 8
KathyX X X9 10 11 12 13 Class 14 SCIENCE 15 16
Required 17 Tim 18 Mark 19 Jen 20 21
View 3 Replies
View Related
Mar 6, 2014
I have in sheet1 the following:
A
B
C
D
E
F
G
H
1
#
SECTOR NUMBER
VALUE
SECTOR 1
SECTOR 4
SECTOR 8
[Code] ...........
And I want to print in columns E:G for each "SECTOR NUMBER" all "VALUES" associated from range B2:C14 and when there is no more values print blank.
I've been trying with following formula and works fine looking values for "SECTOR 1" when I introduce the formula in E2 and copy down, but fails if I copy the formula in F2 and copy down for values of "SECTOR 4", since only prints the first value repeated.
Code:
=IFERROR(INDEX(OFFSET($C$1,MATCH(E$1,$B2:$B$14,0),0,10,1),ROW($A1:$A$10)),"")
View 1 Replies
View Related
Mar 3, 2007
I am using Excel as my payroll spreadsheet
A....name
B....rate of pay
C....date of work
D....date of work
E...date paid
F....# of hours worked
G....vacation hours
H....Gross Pay....B7*(F7+G7)
I....Net Pay....H7-J7-K7-L7-M7-N7
J....FICA....H7*6.2%
K....Federal....no formula
L....State.....no formula
M...Medicare....H7*1.45%
N....duductions....no formula
O....OT...no formula
P....Paid OT....O7*15.75
Q....Blank
R....FICA MATCH....J7*2
S....Medicare MATCH....M7*2 (ISN'T ROUNDING UP OR DOWN PROPERLY)
For Instance.......Joe worked 19.58 hours at $8.50 = $166.43 gross
federal is $0
state os $0
fica is $10.32
medicare is $2.41
R...FICA Match is $20.64
S...Medicare Match is $4.83.....(SHOULD BE $4.82)
Sorry but I didn't know how to properly explain the problem that I am having. The problem is in the rounding. Sometimes it doesn't round up and sometimes it doesn't round down.
View 9 Replies
View Related
Apr 4, 2007
I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing.
I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows:
=SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0")
To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.
View 9 Replies
View Related
Jun 12, 2008
how to use a cell value in a formula with VBA.
For example, I have the following
FinalRow = Cells(65536, 1).End(xlUp).Row
CYGP = Cells(Final Row, 11).Column
Cells(FinalRow + 2, CYGP).Select
I now need to be able to use the value that's located in the selected cell elsewhere in the spreadsheet. I've tried the following:
ActiveCell.FormulaR1C1 = "=R[FinalRow]C[CYGP]
This was an attempt to put the value that's in Cells(FinalRow, CYGP) into my active cell, but it's not working...
View 9 Replies
View Related
Mar 24, 2009
In Range A1:E2 I have values
1.89 1.99 2.09 3.40 2.25
1.90 2.10 1.90
I need in Cell F2 a formula to produce the figure -0.33 (1.90-1.89+2.1-2.09+1.90-2.25)
The 2nd row minus the first row, but only include or consider the columns that have a value on the 2nd row
View 9 Replies
View Related
Apr 7, 2009
I have a series of worksheets that have materials displayed as shown below:
Column A Column B Column C
Material A
material B
Material V
Material F
Total Price 123456
Material C
material B
Material V
Material G
Promo Price 123456
I would like to know if there is a formula can perform a lookup based upon the material in column A and then return the Total price in column C. For each material in column A there is a price but only 1 total price for each grouping.
View 9 Replies
View Related
Nov 29, 2009
I have a sheet i use for a league table, but i need a sheet to be a copy of the league table so i can e-mail it out to various people
so in the original league table i have "=Sheet1!F7" and "=Sheet1!F7" quite a lot
My question is this, can i copy the contents of the original league table only as values and not the formulas?
View 9 Replies
View Related
Dec 1, 2009
Need a formula that will subtract the values in X:Z subtracting the lowest value from the second lowest, then using that value and subtract from the 3 value. The outcome will always be positive and range from 0-9. Expected results are in Col AA. Then I need an adaption of that formula to read a 3-digit value in one cell.
******** ******************** ************************************************************************>Microsoft Excel - CASH 3 MID MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutW1=WXYZAA111/30/094774211/29/099636311/28/091632411/27/097687511/26/095775611/25/097801711/24/093003811/23/097700911/22/0952851011/21/0990361111/20/09346512 13 1411/22/095285 1511/21/099036 1611/20/093465 Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jan 25, 2007
I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.
The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.
I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.
=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).
This produces a blank or error result.
=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))
This produces an error whatever is in the J column
View 9 Replies
View Related
Mar 8, 2007
I need to create a formula where if (example) letter A was placed in cell A1, cell A2 would assign (example) value of 1, but if letter B was placed in cell A1, A2 would assign different value.
Ideally the formula would be able to handle multiple values from a range on seperate sheet.
View 3 Replies
View Related
Jan 16, 2008
This question pertains to the solution offered by Dave Hawley in the following post: Sum Unique Values That Exist In Another Column. The original question relates to summing certain values in a column based on unique values in an adjacent column. I have tried the solution on my own, and it works quite well, however, I just can't understand how the "criteria" in column C functions.
ID XXX
1 100
2 200
1 100
3 500
4 600
2 200
--------------------
Total 1400
Assume your 2 columns are A & B and you wish to SUM column B where any unique value exists in the same row but Column A. Add headings to A1 & B1. In C1 Enter the text Criteria. In C2 Enter this formula =OR(COUNTIF($A$2:$A$100,A2)=1,COUNTIF($A2:$A$100,A2)>1). In any cell Enter =DSUM($A$1:$B$100,B1,$C$1:$C$2)
View 3 Replies
View Related