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
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 |
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")
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"))
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")
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.
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
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
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]....
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?
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?
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.
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).
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies View RelatedI'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
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)),"")
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.
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.
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...
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
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.
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?
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.
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
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.
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)
IF formula to transfer specific text and values from sheet 1 to sheet 2 (please refer to attachment). Sheet 2 should display the name, state and unprocessed rate for each month for individuals with a rate of 95% and 30 or more unprocessed files.
Unprocessed Files.xlsx
So I have this formula to basically lookup the letters in column L and if certain letters exist, then change those letters to another letter. Here is my Formula:
=IF(L1="A",IF(ISNUMBER(MATCH("B",$L$1:$L$200,0)),"B",L1),IF(L1="D",IF(ISNUMBER(MATCH("E",$L$1:$L$200,0)),"E",L1),
IF(L1="L",IF(ISNUMBER(MATCH("L",$L$1:$L$200,0)),"O",L2),IF(L2="F",IF(ISNUMBER(MATCH("K",$L$1:$L$200,0)),"K",L1),L1))))
For example: if column L looks like
A
B
A
E
D
Then it would convert the A's to B's since there is a B present and D's to E's, but if the B and E hadn't been in the equation then it would leave the A's and D's as they are. WIth this formula the output looks like:
B
B
B
E
E
My problem with this formula is that it only recognizes the first letter in each column. Sometimes I have multiple letters in a column and I need it to recognize them and change accordingly.
For example:
A
D_B
E
I would like to have it change to
B
E_B
E
I have attached a sample workbook that may make it more clear... If a VBA code would work better then I would be open to that as well. Combining question.xlsx
Looking for formula to extract values from column A, D through K from raw data table if conditions are met. Desired outcome is in green color.
Sample data attached : Pull.Sample.Data.2008.xlsx‎
Outcome
WestTarget
ProductQ1BBQ1Q2BBQ2Q3BBQ3Q4BBQ4
Product 1348477119150
Product 2578251973823
Product 8763949644583
I have a spreadsheet where I have two different sheets; a summary sheet, and a more detailed sheet that lists out items on invoices.
The summary sheet shows invoice number, amount invoiced, and total paid against that invoice.
The detailed sheet goes into more details, and shows whats on the invoice, and any reasons for them not being paid.
I want a formula on the summary sheet that picks up the invoice number from the details sheet, and then brings through the figures. The details sheet may have more than one line for an invoice number, so the formula needs to be able to add up too.
I've tried a vlookup, but it doesnt seem to work, and I dont think it will do what I want.