Multiply Cells Of Table Range
Apr 3, 2008
I need to use here.
Left column will be numbered 1, 2, 3, 4, etc
Top row will be beginning with $8.00, then 6.20%, then 1.45%, then 0.60%.
By me multiplying $8.00*6.20% = 0.50
$8.00*1.45% = 0.12
$8.00*0.60% = 0.05
Which then brings to my total of 0.66 (when adding all the totals)
$8.00 6.20% 1.45% 0.60%
1 $8.00 0.50 0.12 0.05 0.66
2 $16 0.99 0.23 0.10 1.32
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
View 3 Replies
ADVERTISEMENT
Aug 20, 2014
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies
View Related
Apr 25, 2014
I have a Pivot table showing costs of several types of items.
The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).
So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.
Can this be done in Excel 2010?
I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.
View 3 Replies
View Related
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
Jun 9, 2006
After ripping a .pdf report into Excel I am left with a variety of items in Column A, numbers as text, text, and blanks. I manually selected entire column and paste special multiply. Seems to have convereted blanks to zero, converted numbers stores as text to numbers, and ignored any text. Problem is writing macro to repeat the steps as will use the tool every period. I tried looping through the range
For Each C In myRange
C.Value = C.Value * 1
Next C
But this returns type mismatch error. So why did it work manually if won't work through the macro?
View 2 Replies
View Related
Oct 20, 2006
I try to multiply a range with a single value then put the result to another range. But I got the error. range("a1:a10").value = range("c1:c10").value * 5. the error message is the type dismatch. How to do that simple task?
View 5 Replies
View Related
Nov 22, 2006
I have a question regarding a vba formula in excel. I am trying to take a whole row ( range) of data and multiply each cell within that range by -1 if a particular cell is an Asset or Liability.
Function AFTER_REFRESH(Argument As String)
AFTER_REFRESH = True
If Range("G5:H5").Item(1, 55) = "LEQ" Then
Range("G5:H5").Value = (-1) * Range("BM5:BN5")
MsgBox "Yo"
Else
End If
End Function
Would I have to use an array?
View 7 Replies
View Related
Feb 21, 2008
After formating I need to multiply by 2 . As you can see my Cells.Value = Selection.Value * 2 is not correct. How can I accomplish this?
With Range("I17:I4000", Range("I" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=ReplaceChar(I17)"
.Value = .Value
Selection.NumberFormat = "0.00"
Cells.Value = Selection.Value * 2
End With
View 9 Replies
View Related
Jan 5, 2010
how do I multiply a variable to a range?
I've tried the following code but that does not work. My variable name is CurrRate
with range("D7:D207, G7:G207")
.value = .value * CurrRate
end with
View 9 Replies
View Related
Jul 17, 2009
Please see the attached .xls.
I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.
Basically there is a 2 dimensional array of weights (range1, limits)
each range in range1 and "limits" has a midpoint
a constant value (Fixed Amt) is added to each midpoint in range1
then multiplied by another constant value (Inflation Index)
The value is then capped by corresponding value in the limits range
this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results)
and the sum is returned.
I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.
View 6 Replies
View Related
Apr 20, 2012
I have a column of values I need to multiply by a random number (percentage) within a range of numbers (15%-22%).
How can this be done?
View 1 Replies
View Related
Apr 22, 2014
I am trying to show a blank cell if the others don't have any figures in there and am using the following formula.
However, in my cell it is showing "#value" instead. How do I get my cell to look "blank" when there are no values in the other cells? Here is my formula
=IF(SUM(A17*D17)>0,SUM(A17*D17),"")
View 6 Replies
View Related
Mar 21, 2014
I cant seem to multiply two groups of cells at the same time. Its a hard one to explain so this is what I have tried to use and it doesn't work
=sum(a1:a5*5),(b1:b5*10)
so I tried...
=sum((a1:a5*5)(b1:b5*10)) and that didn't work either
what I want to do is add a1-a5 then * 5 and add it to the total of b1-b5*10.
View 8 Replies
View Related
Nov 22, 2007
I've tried hard to come up with a title as accurate as possible. Here is my problem:
I have six cells that need to be summed (N9,Q9,T9,W9,Z9,AC9). The sum of the cells needs to be multiplied by however many cells that have numerical data in them divided by 6. Assuming all cells in the range (N9,Q9,T9,W9,Z9,AC9) have the value of 50 the following rules would apply: ...
View 3 Replies
View Related
Oct 15, 2009
I'm absolutely new to Excel and I've been trying to workout a formula to to test two conditions if true and derive the data from a 3rd column. In my attached excel file there's a Grade (A) , Status (B) and Mid Point (C) Tables. I'd like it to be formulated in a sense that forexample:
IF Grade= 1 AND Status= Q4 RETURN =3750*0.25
IF Grade= 1 AND Status= Q3 RETURN =3750*0.5
IF Grade= 1 AND Status= Q3 RETURN =3750*0.75
IF Grade= 1 AND Status= whatever (I mean whatever I input in this cell then it would retrun 3750 ofcourse satisfying the condition grade= 1) RETURN =3750
IF Grade= 2 AND Status= Q4 RETURN =5500*0.25
IF Grade= 2 AND Status= Q3 RETURN =5500*0.5
IF Grade= 2 AND Status= Q2 RETURN =5500*0.75
IF Grade= 2 AND Status= whatever (I mean whatever I input in this cell then it would retrun 5500 ofcourse satisfying the condition grade= 2) RETURN =5500
................
View 7 Replies
View Related
Feb 9, 2013
How do you multiply two column vectors D2:D6 and G2:G6 based on reference cells A3 and B3 that are equal to D1 and G1?
A
B
C
D
E
F
G
1
2
3
4
5
[Code] ......
View 1 Replies
View Related
Sep 23, 2009
I am having a little trouble with multiplying a few formulas. I am looking for a formula that will multiply the last three cells in a row that contain data and subtract 1. Below is an example of the type of data I am working with and the formula I am trying to use but is not working. The formula is for the cell highlighted in red. Every quarter the last three cells being referenced will change.....
View 9 Replies
View Related
May 30, 2009
I have the following table:
Month / Year / Value
JAN20060,73
FEB20060,76
MAR20060,76
APR20060,76
MAY20060,73
JUN20060,69
JUL20060,69
AUG20060,73
SEP20060,76
OCT20060,75
NOV20060,74
DEC20060,72
JAN20070,67
FEB20070,68
MAR20070,71
APR20070,75
MAY20070,75
JUN20070,74
JUL20070,71
AUG20070,68
SEP20070,67
OCT20070,7
NOV20070,73
DEC20070,75
I want excel to multiple the values in a specific range.
For example:
I will give 2 dates: MAR 2006 and MAR 2007. Excel should multiple the value 0,76 and 0,71. It's simples because is 2 months. But I have a lot of months (2006 until 2009).
So I would give MAR 2006 and MAR 2009 and excel should multiple the values from MAR 2006, 2007, 2008 and 2009. All the values in the range, but only for March.
The month will never change between the first and the last date.
I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.
View 11 Replies
View Related
Apr 17, 2007
I am trying to output tables from excel to an HTML file using the following subroutine. It creates the table as I would like, but I am having a brain cramp trying to figure out how to get the cell data into the routine. The part in error is in the code below, in Red. I need some way of entering something like " Sheet(SheetNum)&.Range(Column&Row)&.Value"
Private Sub CreateTable(Center As Boolean, Border As Integer, Width As Integer, SheetNum As Integer, StartRow As Integer, EndRow As Integer, StartColumn As Integer, EndColumn As Integer)
Dim Row As Integer
Dim Column As Integer
Dim ColumnID(1 To 15) As String
Dim CellLoc
ColumnID(1) = "A"
ColumnID(2) = "B"
ColumnID(3) = "C"
ColumnID(4) = "D"
ColumnID(5) = "E"
ColumnID(6) = "F"
ColumnID(7) = "G"..................
View 4 Replies
View Related
Dec 6, 2013
I would like to only view (or otherwise mark) the highest value cells (in column g) for each category (column c),
View 3 Replies
View Related
Dec 2, 2013
I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
View 2 Replies
View Related
Aug 6, 2009
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
View 8 Replies
View Related
Mar 4, 2008
Is anyone able to see where i went wrong, i am tring to make a function sumproduct using dynamic ranges. i can do the same thing using arrays which is simple(below), but not using ranges, below. the range is dependent on a the number of selected choices, hence the ubound.
View 8 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Jul 21, 2013
I have a table thats acting as a database analysing player data and I have a second table in a report sheet based on the database table. In my report table I have a drop down with the player names at the top and I want the data cells underneath to populate based on that particular players data from the database.
View 1 Replies
View Related
Oct 28, 2009
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
View 3 Replies
View Related
Mar 17, 2014
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
View 2 Replies
View Related
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
May 22, 2008
I have a table with data which I have entered with VBA, now the only thing missing is multiply column D with column E.
When the data gets put into the table I like the data in column E to be multiplied with the date from column D.
View 9 Replies
View Related
Aug 10, 2006
I am having trouble creating a formaula to calculate value based on the sum of a cell. For example if the sum of cell A1 is <= 149 I need to multiply the sum by 1.0
if the sum of cell A1 is 150-199 I need to multiply the sum by 1.50, if the sum of cell A1 is 200-249 I need to multiply the sum by 1.75, and if the sum of A1 is >= to 250 I need to multiply the sum by 2.00
View 3 Replies
View Related
