Excel VBA Array Scalar Multiplication And Addition

Oct 20, 2012

I have two columns of data in an excel sheet that are equal in size and both only contain numbers.

I'm trying to write a macros which will put both of these sets into arrays, then perform a calculation on them.

Specifically ArrayA + 3*ArrayB then put the result back into the worksheet in a new column. Below is the code I have so far.

Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC As Variant

ArrayA = Worksheets("Sheet1").Range("A1:A5")
ArrayB = Worksheets("Sheet1").Range("B1:B5")

This is where things go bad
ArrayC = ArrayA + 3 * ArrayB

View 4 Replies


ADVERTISEMENT

Multiplication/addition Function

Jan 27, 2009

I obviously know less about functions than I thought I did. I've got the attached spreadsheet set up except getting totals at the bottom. The production total L44, would be column A multiplied by the quantity entered in columns L and summed. Same for Total SF, square footage in column B times quantity in L and summed at the bottom. This would continue daily, needing sums under each column.

View 4 Replies View Related

Basic Addition, Subtraction & Multiplication

Apr 12, 2008

Take a single cell in column D, and multiply it by a single cell in column E, which will equal F. Take column F, and multiply it by .02 (2%), which will equal G. Take a cell in column G, and subtract it from F, which will equal I. And this all takes place in the same row. Then have it move down to the next row, and do the same thing..... so it would basically look like this.....

A B C D E F G H I
1 D1 E1 (D1*E1) (F1*.02) (G1-F1)

2 D2 E2 (D2*E2) (F2*.02) (G2-F2)

3 D3 E3 (D3*E3) (F3*.02) (G3-F3)

For easier reading.... in each row I want it to do the following math
D*E=F
F*.02=G
G-F=I

And then do it for every row that I have data in (excluding the VERY first row). I am -COMPLETELY- sorry if I broke any rules, and am also sorry for the poor representation

View 5 Replies View Related

Summing The Multiplication Of One Array Times Multiple Arrays

Jul 24, 2008

I am trying to multiply one array of prices for multiple input products across
the volume that that product my go into multiple end products.

I know you can use SUMPRODUCT with two arrays that are equal BUT I want to Multiply the price matrix across 15 or 16 other matrices and sum all the products.
The price matrix and the other arrays are all 1 x "whatever".

View 9 Replies View Related

How Do I Perform An Array Multiplication If A Cell Contains A Specific Text String?

Feb 17, 2009

Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.

Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].

View 11 Replies View Related

Excel 2010 :: How To Exclude Colored Cells In Addition Equations

Aug 6, 2014

Any way to exclude colored cells from addition in a column. I will attach a simple example document.

I am building a spreadsheet to keep track of my monthly spending, and debt balance. I keep track of the total amount of bills I pay each month. As I pay them, their respective line is highlighted green in the spreadsheet. I would like to create a macro/formula which would sum the remaining cells in the column which are not highlighted to display the remaining monies which need to be paid out. I'm not sure if this matters but I am using office 2010.

billSample.xlsx‎

View 4 Replies View Related

Make A Calculation(addition) And Use The Answer To Multiply Against Another Addition Calculation

Nov 19, 2008

make a calculation(addition) and use the answer to multiply against another addition calculation....

The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.

View 2 Replies View Related

Getting Sum Of The Multiplication Of Two Columns

Jul 10, 2014

I want to get the sum of the multiplication of two columns. Why the macro is needed?

Because I want always to multiply the first column with a series of column combinations. At the end I want to get a number for each case.

Example.

I have 4 columns (this can vary). I want to perform:

A*B, A*C, A*C, A*(B+C)

View 4 Replies View Related

VBA Multiplication Of Two Columns?

Oct 6, 2012

what i am trying to do is multiply the numbers in two seperate cells on the same row (e.g. C1*D1) and do this for the whole column where there is data (so it could go down to (C9*D9)

I was wondering how I would execute this in a macro, I am also looking to show the total value of all these added together but one step at a time

View 8 Replies View Related

Multiplication Formula

Feb 19, 2009

As I have illustrated in the example below, I want to multiply a value by 1.(something). The 1. is always constant, where the I8 cell is variable. I can't get this to work.

=H15*1.(I8)

View 9 Replies View Related

Values Multiplication

Jul 8, 2006

I have a worksheet with many lines of inputted numbers (values). I want to divide each number in the sheet by 4. However, I don't want to move any of the numbers because other sheet in the workbook refer to these cells. Does anyone know how i can do this easily?

View 3 Replies View Related

Creating Multiplication Table?

Aug 1, 2014

Using array

Part 1:

1. Create a Multipication Table up to 10x10.

2. Store the values in a multidimensional array.

3. The program should ask the user what two numbers from 1-10 would he like to multiply.

4. The program should not multiply the two numbers but instead use the two numbers as reference for the element number and locate the corresponding element.

5. The corresponding element should have the value same as the product of the two numbers entered by the user.

Part 2:

1. Do the same as Part 1 but this time automate the creation of the multiplication table using the concept of array.

Here's what I've done so far for part 1

[Code]...

But when I enter the two numbers It just displays the value in cells(2,6)

Attached File : table.xls‎ ..

View 1 Replies View Related

Inverse And Multiplication Of Matrices In VBA

Nov 30, 2008

1) I have declared:

Mtrx(20,20) as Integer, where i have a Matrix

1 2 3
1 2 3

How can I do an inverse of this matrix using only VBA commands?

2) I have :
Mtrx(20,20) as Integer, for example :

3 1
2 0

and Mtrx1(20,20) as Integer :
1 2 0
0 1 1

How to multiply these matrices using only VBA ?

View 9 Replies View Related

Attributing Values And Multiplication

Apr 25, 2007

I am writing a spreadsheet as a report for a customer and i want to show the following in the sheet.

A customer buys 12 of XProduct (retails £2)
A customer buys 15 of YProduct (retails £3)
A customer buys 20 of ZProduct (retails £5)

I want the sheet to have columns stating - Amount Bought - Product - Gross Amount.

And i want to be able to have the product column intelligent enough to know that if i type any of my products sold it will automatically attribute the correct cost (i.e. I type in Xproduct and it knows that value is £2) so that in the Gross Amount column it will all make sense, and save me having to manually put in the retail cost each time which will occasionally change. At present i have the same three columns and i multiply the Amount Bought with what i know is the cost and do the sum myself and then enter the result in excel -

View 9 Replies View Related

Multi-Criteria Multiplication (multiply (x * Y * Z *)

Aug 4, 2009

I have three reference cells (a1, a2, a3).

Below, starting from b4 to l4 I have years from 2005 to 2015, row 5 for the same range contains data, row 6 (c to l) contains a growth rate.

I need to come up with a formula that can multiply (x * y * z *... [product-like formula]) the growth rates for all the years where the current year (row 4) is bigger than reference the reference year in a1 or smaller than reference year in a2. A3 is then used to multiply that total.

View 6 Replies View Related

Multiplication To Ignore Minus Numbers

Oct 7, 2009

If I wanted to get a cell, say A1, to multiply a value, for example 7, by the results in another cell, say B1, but NOT multiply if that cell's value is a minus number, is there a way of getting Excel to do that?

Normally I'd just type =SUM(A1*B1)*7, but how can that be reconfigured to ignore minus numbers? e.g. if B1 contained -5, ?

View 10 Replies View Related

Creating Random Multiplication Table

Aug 9, 2013

Here's what I'd like to do:

Have 10 columns... one column would be for the "1s", another for the "2s", another for the "3s", etc...

But I don't want to have them in straight order (i.e. 1 x 1 =, 1 x 2 =, 1 x 3 =, etc...) because the pattern is easily memorized, no matter what number he's working on.

I'd like to be able to hit a button (or something) and have each column shuffle (or randomize) the order in which the equations will be displayed.

So, a column might come up 3 x 6 =, 3 x 9 =, 3 x 1 =, 3 x 4 =, etc...

And next time it might come up 3 x 5 =, 3 x 2 =, 3 x 4 =, etc...

This way he won't be able to memorize any patterns and he'll actually need to learn his multiplication tables.

View 14 Replies View Related

VBA Noob Multiplication Isn't Working With Decimal

Apr 2, 2009

I just started using VBA and WOW at the possibilities!! I have some data I am trying to calculate and decided upon researching VBA that this would be the easiest way to do what I need to. The code goes through no errors but D2 is .0001 and N2 is 12000 and the answer I get when I multiply the 2 through VBA is 0.000 however if I change the number in D2 to 2 then I get 24000. I tried the help menu and possibly this is because I didn't do something right with the decimal?

View 3 Replies View Related

Can't Multiply...why Is Simple Multiplication Incorrect?

Nov 7, 2008

On the PROPOSAL tab, at I41, a simple multiplication instruction is wrong. It's multiplying 72*186.53 which = 13430.16 in the real world, but on this sheet 11 cents are missing. I'm pulling my hair out on this one....

View 4 Replies View Related

Range Multiplication - Values Multiplied By 1.07?

Sep 25, 2011

In a range ("A1:A30") are values, What I need to do is have those values multipled by 1.07 and the new value incerter in the cell using VBA, I dont wantto use a formula.

View 5 Replies View Related

Multiple Stage Multiplication In Single Cell

Dec 18, 2013

I have to maintain freight cost according to the shipment weight. Our shipper has different charges for different scale/quantity of shipment. For example, according to the attached picture; Rate for up to 10 Kilo is $ 20 per kilo. For weight more than 10 kilo and up to 20 kilo, Rate will be $ 15 per kilo and so on.

Now, suppose my shipment weight is 28 kilo. My cost will be $ 200 for 10 kg, $ 150 for next 10 kg and $ 80 for the last 8 kg. In total $ 430.

I have to calculate the total cost separately. I wonder if I could devise a formula by which I could put the weight in a single cell and get the freight cost instantly.

View 2 Replies View Related

Solving Multiplication Of Two Matrices With Entries Decimal

Mar 16, 2014

Problem solving multiplication of two matrices with entries decimal in vba code:

Code:
Option Explicit

Public MatrixA(30, 30) As Long
Public MatrixB(30, 30) As Long
Public MatrixC(30, 30) As Long
Public RowA As Integer
Public ColA As Integer
Public RowB As Integer
Public ColB As Integer

Function ProperMaticesSizes() As Boolean

[Code] .........

View 1 Replies View Related

Recognize Conversion (word To Number) And Multiplication Factors?

May 20, 2009

I'm trying to make a table of the total amount of a liquid used throughout the day. Here is what I am trying to do: In cell D4, I want to be able to enter something similar to the following: 3cup+2bottle+1liter

and by doing so, Excel can automatically recognize that 1cup is 8oz, 1bottle is 17oz, and 1liter is 34 oz because of the reference chart provided on the side. Also, it would be able to notice the 3, 2, and 1 amounts so it would multiply accordingly so it would know to do this: (3*8)+(2*17)+(1*34)

and then put the calculated amount in the cell. The correct answer should be 92oz. Is there a way for Excel to recognize the conversions (i.e. whenever it sees 'cup' it will multiply by 8) and multiplication factors (i.e. 3, 2, 1)?Is there a formula I can enter that I can just "drag" down to the upcoming days in column D?

I know I can just do something like this: (3*G4)+(2*G5)+(1*G6).

View 2 Replies View Related

Addition Of Cells

Feb 8, 2007

I wanted to have the weeks of the month down one column = 52 week.

down the next column I have different amounts of money in that week.

some months have 4 weeks and other have 5. I wanted a program to say:

If you see a month "x" look at the next column and take that amount. Then on the next row you have month "x" again (week 2) go to the next column and take that amount and add it to week one. And so on until all 4/5 week are added to give on result.

Then the same for the next month...
month amount/week amount/month
05-Mar 0
12-Mar 70
19-Mar 210
26-Mar 350 1050
02-Apr 420
09-Apr 455......

View 9 Replies View Related

'Remote' Addition

Mar 16, 2009

I am hoping someone with excel experience can be of help to me with an unusual request for excel.

Assume cell A1 = 2, B1 = 3 and i wish the sum of this (5) to appear in cell C1. Very straight forward so far, however i wish the result to appear in C1 when i left click on a cell other than C1, say for example D7.

I can't use any macros for this.

View 11 Replies View Related

Sumproduct - Addition By Name

Jun 11, 2009

I need to C8 - C19 only to add up jobs won by andrew (in current orders). It needs to be month specific. what i mean by that is I need the formula to do what its doing now (adding up the jobs by and putting the totals into the according cell depending on what month they were won.

View 2 Replies View Related

Addition In Base 6

Jun 14, 2009

I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)

View 2 Replies View Related

Vlookup & Addition

Dec 28, 2009

I have multiple ranges in a spread sheet. I am trying to write a formula that will go out to each range in succession and look for a part number, upon finding return a quantity and them move on to the next range duplicating the above process. The formula should tally the grand total of all numbers found. I have it working except that not all of my items are in all ranges. If the item that I am searching for is in all ranges my formula works but if there is one or more of the ranges that doesn't have that particular value it returns an #n/a instead of totalling those that do have it. If I use a true instead of false in my [range_lookup] I get an incorrect answer. My formula for a given cell is listed below. This is with the true argument which does not work....

View 9 Replies View Related

Addition Formula

Aug 13, 2008

I am a new excel user. I a trying to write a certain formula but am having trouble. I want to write the formula to add a column of numbers, say H-10 through H-15. Each cell will have a number in it, but I want only to add the cells if the cell precedding it in the G-10 through G-15 Collumn is blank. For example if cells G-12 and G-14 have an "X" in them, then I do not want Cells H-12 and H-14 to be added. I only want the formula to add cells H-10,H-11,H-13, and H-15. I used just 6 cells for example, the column of cells to be added will be a lot longer.

View 3 Replies View Related

Algorithm For Addition (operation For Each ID)

Sep 25, 2009

Have an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)

201688194000bottles
20168819200crates
2016883812000bottles
20168838600crates
201688396400bottles
20168839320crates
201688809000bottles
20168880600bottles
20168880480crates...................

I need to write a macro which will do this operation for each ID:

(bottles/20)-crates = x

and if "x" is not 0 then write down the value of "x".

There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)

View 3 Replies View Related







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