Multiply Corresponding Numbers & Return Sum Of Those
Jul 28, 2007
I managed to put in a long formula in col G for multiple criteria in my worksheet. I am still puzzled as to how do I caculate the separate two Total Sums of these below:
1) if values in col G is numeric, using value in col G X unit price (answer is 124955.54)
2) if strings in col G start with text "OH_excess", retrieve its number, then X unit price (answer is 209690.95)
I did a search myself, then went through the 5 pages the "Post" function brought up. I'm sure it's been asked before, I must just not be using the right words
I need to tell every cell in one column to multiply itself by a firm number and a percentage. Ex: Column F will be the result of $77.80 times 75 percent all the way down the column. Column G will be $27.21 times 100 percent all the way down.
I could enter the formula into every cell, but I wondered if there's a way to just tell the whole column, only once, what it needs to do. PS-I work for an insurance company. The employees of a company want a spreadsheet showing exactly who pays exactly what. Ex: Their paystub shows $142.90 coming out of their paycheck. Their employer pays 100% of medical, 75% of dental. The columns would show Total, Employer Medical, Employer Dental, Employee Medical, Employee Dental.
I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:
i have a column of points awarded in golf scoring sheet, 100,80,70,60,50,40, and so on, I want the 100 to be 1st, 80 to be 2nd, 70 to be 3rd, but to return the name of the person in column next to that particular score, ie, Joe smith gets 100 points so he will be first and to show up on another page in workbook.Only his name need show up
In a row i have : A1= 450B1=560C1=500D1=510E1=445F1=430G1= 420. Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum. The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that?
I'm working on a productivity report, on which some employees may produce units for more than one team. The report is organized by team.
I'm already totaling the number of units for each employee which I'm listing in alphabetical order.
But, I would also like to do the following:
a) see the number of occurrence/teams each employee is producing units for. b) see the row numbers for each occurrence, which would normally be three or less. c) see the value for each occurrence.
Is there formula that I can use that would have Excel count the numbers in a colum and return the numbers that are missing, 1,2,3,5,6,7,9,10 and excel returns 4,8?
I am looking for a formula to extract numbers from a list in descending order. As the list may have more than one entry of the same number the formula i need should only show one of that particular number.
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows.
Columns are: ID- a unique iderntifier for each entry- integer Date- date the entry was made- date Project Number- a reference to an internal project file- string Operator- company we were working for- string Installation- where the work took place- string Category- type of problem encountered- string Application- field of problem encountered- string Issue- string Background- string Problems- string Lessons Learned- string...........
I am trying to return a number with a specific format. I need Excel to look in a particular column and when it finds the entry in the correct format to return it in a new column. I want it to repeatedly return the number to the new column until it finds another number of the same format in the original column.
Say I have a certain range in excel from A1:C10. All of these cells contain one of three strings: Math, English, or Reading.
Given an input of ONE of these three (for example, a user can select "Reading"), the program should output ALL the row&column numbers of each occurrence of "Reading" in the range. Ideally, this would appear in a column.
Consider the following image that contains a sample range:
Screen Shot 2014-07-08 at 12.26.15 PM.png
If "English" is the input, the output should be (in another column)
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels.
I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels.
I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result.
I have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.
The same thing would go for A11:A20 & D11:D20.
If the number in D10 is 1-2 then A10 needs to display 0 If the number in D10 is 3-8 then A10 needs to display 0.1 If the number in D10 is 9-14 then A10 needs to display 0.2 If the number in D10 is 15-20 then A10 needs to display 0.3
Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.
Column A consists of cells with a variety of five digit numbers. My formula needs to return a text value if numbers are present in column A, this is what I tried: =if(A1="","Chassis") but it returns "False" instead of "Chassis".
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E 3 4 / / 5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.
I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.
Example:
A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5 A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10
I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.
I have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
By "Return all row numbers that have a given value written somewhere inside text in their column B" I mean....
I want to be able to search an alphanumeric value "ABC12345" within a column that has a paragraph of text written in each row, I want to know which rows have this value written somewhere in them and have it return 2,4,33, as row 2 and 4 and 33 have this value in them inside column B on that row.
on column A i have the respective row numbers all the way down ie: ( 1,2,3,4, etc) (to pull up the row numbers)(unless you have a better way)
On Column B i have the paragraphs of text to search in (there is lots of text and spaces in the paragraphs.)
On column C i have the value to look for... ie: ABC12345 (different values in each row of column C) to be compared to all of column B
On column D would be the formula to Pull down and get the results.
and something tells me you might have a better way of getting the row numbers rather than having column A dedicated to numerating each row 1,2,3,etc
1 paragraphs of text look for it's found in row #
2 anything can be written here and might have ABC11112 DDD77777 4
3 anything can be written here and might have ABC12345 ABC55555 5,6
4 anything can be written here and might have DDD77777 BBB11111 Not Found
5 anything can be written here and might haveABC55555 ABC11112 2
6 anything can be written here and might haveABC55555 ABC12345 3
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.