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

Using One Formula To Classify Stock Quantities?

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

VBA To Classify And Color?

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

Count IF To Sort / Classify?

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

Classify A Number Into Range

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

Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

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

Formula For Getting Average Of Values Without Counting Zero Values In Range?

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

Formula To Find The Sum Of Values That Were NOT Equal To My Quoted Values

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

SUMIF Formula That Is Collects Values From An IF Formula

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

Formula For Obtaining Values On Separate Sheet From Values Declared On Current Sheet?

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

Max Values Formula?

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

Formula For Duplicate Values?

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

Extract Values Using Formula?

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

Formula To Find Value In Between Two Values

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

Values Into Colours (IF Formula)

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

Using Two Lookup Values In One Formula

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

Formula To Add Unique Values

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

Formula To Capture All Values >=50 To <=99 In A Row

Feb 27, 2007

I would like a formula to capture all values >=50 to <=99 in a row.

View 14 Replies View Related

Formula For Values In A Matrix?

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

Formula To Print Associated Values

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

Formula Round Up The Values

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

Formula Average The Values

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

Using Cell Values In A Formula With VBA

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

Formula- In Range A1:E2 I Have Values

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

Formula- Find Values

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

Copy Values And Not Formula

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

Formula: Subtract The Values

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

If Formula To Test For 3 Values

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

Formula For Multiple If Values

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

Sum Unique Values Formula

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







Copyrights 2005-15 www.BigResource.com, All rights reserved