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
ADVERTISEMENT
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
Aug 13, 2009
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies
View Related
Aug 3, 2006
Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies
View Related
Jun 10, 2009
I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
View 4 Replies
View Related
Apr 15, 2012
I have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
View 7 Replies
View Related
Aug 8, 2008
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
View 2 Replies
View Related
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
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
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
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
Jun 10, 2007
I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
May 16, 2013
I want to automate the Display labels in my Charts using VBA Coding…
I would need to use this small code in Charts with different Scales as some of the Scales in my Data are in a very large Range close to Trillions so its not possible to have these as the Primary-Y-Axis Scale and therefore I would need to use the in-built Display label option which Excel provides..
However, I always prefer to have the scale in the Range of Thousands so the Max I can have in the Scale can be 10,000 so if there is any Number more than that or the Scale is going above than I would prefer that this gets auto-calculated to Multiply with the remainder and show appropriate Scaling.
For Ex:In this example My largest Number is 4500000000 which is 100 M..So I would want the scale to show 4500 and the Display label as X Millions..
So The Display Label would be different for different Ranges but the Max Scale preffered would be 10,000.
View 8 Replies
View Related
Feb 27, 2014
I need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.
For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00
Lets say the number is in cell A1. What formula would do this?
View 3 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
View 9 Replies
View Related
