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)

View 3 Replies


ADVERTISEMENT

Multiply Numbers & Return X Percent Of Result

May 30, 2009

I have an inherited formula and I am not sure if it is giving me the correct answer. It is:

=0.03*668*0.75*0.10
The result is 1.503

What I am aiming for is to get 3% and 25% of 688, deduct the results from 688 and then get 10% of that answer.

Is the inherited formula correct?

View 3 Replies View Related

Multiply Three Columns Of Numbers Together

Feb 23, 2010

I would like to multiply every number in one column with every number in another column with every number in a third column (columns A,B and C).

There are less than ten cells in each column.

View 9 Replies View Related

Multiply Column Of Numbers By Fixed Number

Mar 1, 2008

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.

View 2 Replies View Related

Excel 2000 - If A1 Equal Or Less Than 1000 Multiply By B1 Otherwise Multiply By C1

Oct 30, 2013

Excel (2000), what formula would I use to accomplish the following?

If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.

View 9 Replies View Related

Address Of Smallest 5 Numbers (return The Value In Column 1 For The Smallest 5 Numbers)

Jan 14, 2010

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:

0
3.196408441
4.491972091
4.491972091
4.491972091

Equation:

=INDIRECT(ADDRESS(SMALL(IF((NumRange)=SMALL(NumRange,1),ROW(NumRange),""),1),1))

View 12 Replies View Related

Return A 1st / 2nd Or 3rd From Row Of Numbers?

Feb 10, 2013

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

View 14 Replies View Related

Return Most Frequent Bin Of Numbers

Apr 6, 2008

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?

View 6 Replies View Related

Return Row Numbers Of Nth Occurrences And Corresponding Values

Dec 13, 2013

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.

View 9 Replies View Related

Count Return Missing Numbers

Dec 17, 2009

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?

View 2 Replies View Related

Return Highest Numbers From List

Nov 16, 2011

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.

So in effect

Column A

40078
40025
40001
40003
39972
41224
40025
40078
40001

Hopefull end result

Column C

41224
40078
40025
40003
40001
39972

View 9 Replies View Related

Return Row Numbers From CountIf Action

Jul 4, 2014

Is there way to show me or return the rownumbers from the CountIf action?

See the code below.

Private Function workLoad(user)

'while changing the combobox it parses the name of the user as a String
'by the code below it counts how many items each user got.

Dim how_many_items As Integer

how_many_items = Application.WorksheetFunction.CountIf(Sheet2.Range("A1:A1000"), user)

MsgBox (a)
End Function

View 2 Replies View Related

VBA Keyword Search To Return Row Numbers

Mar 3, 2008

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...........

View 9 Replies View Related

Return The First Two Numbers Of An Alphanumeric String

Dec 4, 2008

Anyone know how to do this?

View 9 Replies View Related

Return Numbers With Specific Format

Dec 14, 2007

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.

View 2 Replies View Related

Return All Row / Column Numbers Of All Occurrences Of A Value In Database

Jul 8, 2014

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)

A1
A7
B3
C2
C3

View 5 Replies View Related

Look At Range Of Numbers And Return The Next Number Higher Than X

Dec 7, 2012

To keep is simple. in a1:a5 are values 5,10,15,20,25. In c1 value is 13.

Need a formula to look at range a1:a5

And return only the next number higher than 13. Of course it's a3 or 15.

View 3 Replies View Related

AVERAGE Row Of Numbers And Return Corresponding Numeric Label

Sep 19, 2005

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.

Example: Sample Data
A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90
A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81
Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:
1) Includes zeros to be averaged
2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),
$A21:$K21,0)) but receive the #N/A error message.

View 14 Replies View Related

Vlookup- Company Name And Return Totals For All The Numbers

Jun 10, 2008

I have attached a sample sheet.

I need the forumal to look up a company name and return totals for all the numbers.

View 10 Replies View Related

Return One Number Based On Range Of Other Numbers

Dec 8, 2011

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.

View 9 Replies View Related

Compare Repeated Numbers And Return Greater Value?

Oct 26, 2012

how to return the highest value of repeated controls in column A.

when comparing the numbers repeated in columns "A"

will return the highest value in column "D"

control
plug
block
value
Formula

115
20
1012
28,9
31,2
this is the highest value of repeated controls in column A

[Code] .......

View 3 Replies View Related

Return MAX Of Range Of Numbers With SUMIFS Criteria

Apr 21, 2014

I am trying to get excel to return the MAX number of a range of numbers that have multiple if criteria.

Here is some sample data with the final product:

COLUMN A
COLUMN B
COLUMN C
FINAL PRODUCT

x
a
3
7

x
b
5
7

x
a
4
7

y
a
10
15

y
b
15
15

y
a
2
15

View 2 Replies View Related

If Statement (return A Text Value If Numbers Are Present)

May 13, 2009

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".

View 9 Replies View Related

Return X Characters Based On X Cells Having Whole Numbers Above Zero

Sep 14, 2007

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.

View 8 Replies View Related

Return Matching Numbers By Comparing 2 Ranges

Apr 30, 2008

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.

View 2 Replies View Related

Having Two Date Return Number But Exclude Negative Numbers

Dec 18, 2013

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.

View 5 Replies View Related

From Matching Numbers In One Column Return Value Of Second Cells In Another Cell?

May 19, 2014

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.

View 2 Replies View Related

Return All Row Numbers That Have Given Value Written Somewhere Inside Text In Column B

Jul 24, 2013

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

View 6 Replies View Related

VLookup Random Numbers - Sort Range In Descending Order And Return Relevant Value From Column

Aug 27, 2012

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.

View 3 Replies View Related

Multiply Using VBA ...

Feb 7, 2009

How can I multiply a range with a value and put the value in another range, using VBA ?

I have in some cases 2000 -3000 rows and I need to automate the process.

Please open attached file!

View 6 Replies View Related







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