Sum Array & Multiply
Mar 2, 2008
I'm having difficulty in summing multiple rows (order volume) over many columns (despatched volume) that have a specific constant (price for the week).
Please see spreadsheet attached. I would like row 19 to be achieved as one line, thus removing rows 14-18. see logic in rows 14-18 which drives the row 19 result.
The problem is the volume is over 52 row and the price is over 1 row. Cannot think straight as to how to build the array. No VBA as the result needs to be transparent from a formula.
I'm having difficulty in summing multiple rows (order volume) over many columns (despatched volume) that have a specific constant (price for the week).
see spreadsheet attached. I would like row 19 to be achieved as one line, thus removing rows 14-18. Please see logic in rows 14-18 which drives the row 19 result.
The problem is the volume is over 52 row and the price is over 1 row. Cannot think straight as to how to build the array. No VBA please as the result needs to be transparent from a formula.
View 9 Replies
ADVERTISEMENT
Mar 22, 2007
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.
Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padrão em TestA com _
'resultado na matriz em TestA correspondente à matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............
View 7 Replies
View Related
Mar 29, 2013
I am looking for an excel formula that will allow me to perform the following logic: if a cell value in (M3:BA3)="D" then multiply C29 by the corresponding value in (M29:BA29), then sum all of the products.
View 2 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
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
May 18, 2009
b25
if the amount in b7 is the same or less than 7495 i need it to be multiplied by 10%
b26
if the amount in b7 is greater than 7495 then i need it to multiply the diference by 20%
View 14 Replies
View Related
Mar 12, 2009
I want to be able to enter a value into a cell (within a specified range if necessary) and have it automatically multiply by a set value and overwrite the original entry with the result so making it all happen within one cell.
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
Nov 11, 2006
I need the code for a macro that, with a selection, it multiplies the cell number by “x” amount, keeping the original cell format. I know I can do that using paste special but I prefer a macro to be able to use it on different spreadsheets.
View 9 Replies
View Related
Nov 27, 2006
I have 3 columns. A B and C. In column C I want to input the answer to a formula into each cell using data from cells in their respective rows,
For. e.g.
Cx= Ax- (Bx X 0.8).
I want to be able to input this formula somewhere that will automatically fill out the cells in column C.
Column C is 20 cells long so x would range from 1 to 20.
View 5 Replies
View Related
Feb 8, 2009
I have a cell that is "Start Time." Another is "End Time." And, of course, "Total."
The sum listed in "Total" is multiplied by a cost per hour. Except it does not work. I have attached an example copied directly from the cost sheet.
View 3 Replies
View Related
Aug 8, 2014
I have column A that declares the account as asset, liability, expense, etc. Some of the accounts need to be reversed. I'm having a hard time figuring out a formula or code I can use. Column C contains the values. I prefer VBA as I want to add it to the current macro I already have.
So if Column A has a liability, offset by (0,2), and multiply that value by -1. If Column A has an asset, leave the value as is.
I wanted to use an if-then statement but I'm not sure how to highlight entire columns. The number of liabilities, and assets, etc.
View 7 Replies
View Related
Apr 15, 2009
In my macro, I'm trying to use an input box to determine the factor to multiply another cell by. I also want to only allow entry of 6.0 or less, and only allow, (but not require), a single decimal place. Here's what I've got so far, but it's not working:
View 2 Replies
View Related
Jun 16, 2009
On my Userform i have 2 textboxes. Textbox2 needs to look at textbox1 and if the value in textbox1 is equal to or greater than 25000, then multiply that value in textbox1 by 0.0035 and place the value in textbox2.
View 4 Replies
View Related
Mar 1, 2007
I need to take a number, multiply it by a precentage, & then round it to the nearest multiple of 5.
ex:
A1=142.5
B2=A1*50%,(then rounded to nearest multiple of 5)
View 9 Replies
View Related
Oct 24, 2009
I have a row where C5 is the total of C1:C4. What I am trying to do is if C1 or C2 or C3 or C4 has a number value that I can multiply the cell in question by 2 and then add up all of the cells to give a total in C5. Also, I have a name in A1, but when I select E1 and enter =A1 into the cell then return, I get A1 in the cell instead of the name in A1.
View 5 Replies
View Related
Aug 21, 2012
I have the following text in B5 "deposit fee 0.75%". I have a value of 3500 in B2. I would like a formula where a can multiply the value in B2 x the 0.75% that is contained in the text "deposit fee 0.75%"
I tried to use =B2*VALUE(MID(B5,17,1)), but it gives me #value
View 4 Replies
View Related
Jul 14, 2014
I need a VBA macro to multiply column E and F and deposit the result in column G. The multiplication should only happen when there is a value in the column E. If there is no value in the column E, then the cell in the G column should stay blank.
View 9 Replies
View Related
Dec 9, 2009
I have 3 sheet.
i need to multiply the value sheet 1 and sheet 2.
Sheet1 AB1NameAmount2Kumar1003Siva2004Sasi3005kannan400 Excel tables to the web >> Excel Jeanie HTML 4
Sheet2 ABC1Name MonthComments2kannan5good customer3Kumar10average customer4Siva20ok5Sasi25not bad Excel tables to the web >> Excel Jeanie HTML 4
The answer should be like this
Sheet3 ABC1NameAmountComments2Kumar1000average customer3Siva4000ok4Sasi7500not bad5kannan2000good customer Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jan 18, 2010
I need to multiply text box value and Combo box value, but facing with "Run time error-13,Type Mismatch"
Dim total as Integer
Me.txtQty.Value = ""
Me.txtAmount.Value = Total
Total = txtQty.Value * cboRate.Value
View 9 Replies
View Related
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
Jun 14, 2006
I am trying to add 40% to a row of numbers? All of which are whole numbers, like 19, 27, 32, 49 and so on and I want to be able to highlight that particular row and add 40% to it, so all numbers in the row have the 40% added to the individual number? So, my new row of numbers would look like: 26.6, 37.8, 44.8, 68.6 and so on....(using same #'s from up top) Is this possible or can it be done with ease? Or do I have to manual calculate the 40% and add it in? I am guessing there is a way to do it w/ a condition but like I said I am complete newbie.
View 6 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
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
View Related
Mar 8, 2008
currently have this: = SUMIF(D8:E8: F8:G8:H8,1,D29:E29:F29:G29:H29)
Range is multiple rows
need to get to here:
"multiple" Criteria needs to be able to multiply amount in sum range when greater than 1 and then summed in column I, across each row, then totaled at bottom of column I.
I realize sumif won't suffice.
View 4 Replies
View Related
Mar 21, 2008
I am having a calculating issue with a formula I'm trying to use.
What I am trying to do is to multiply the " sum of one cell" with "another cell" for a total.
The following are the cells and data involved.
A12 = 93 C12 = $44.76
A19 = 93 C19 = =sum(C12*0.05) D19 = =sum(A19*C19)
The formulas looked pretty straight forward. My problem is the total I got in D19 was not what I calculated on a calculator. C19 is $2.24 and the above formula gave D19's total as $208.13 but the calculator's total is $208.32.
I can't figure out what is causing the different totals. If there is another formula I should use for this.
View 5 Replies
View Related
Apr 8, 2014
I need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
IF(ISNUMBER(SEARCH("Gold",B20)),C11,IF(ISNUMBER(SEARCH("Silver",B20)),B11,
IF(ISNUMBER(SEARCH("Platinum",B20)),D11,IF(ISNUMBER(SEARCH("Customized",B20)),E11))))
IF(ISNUMBER(SEARCH("Gold",B20)),C2,IF(ISNUMBER(SEARCH("Silver",B20)),B2,
IF(ISNUMBER(SEARCH("Platinum",B20)),D2,IF(ISNUMBER(SEARCH("Custom",B20)),E2))))
IF(ISNUMBER(SEARCH("Gold",B20)),C12,IF(ISNUMBER(SEARCH("Silver",B20)),B12,
IF(ISNUMBER(SEARCH("Platinum",B20)))),)),D12,IF(ISNUMBER(SEARCH("Customized",B20)),E12))))
View 4 Replies
View Related
