Lets say I have this....
PHP Code:
Dim B as Range
set B = activesheet.range("A1:C3")
Dim a(3) As Double
a(1)= 1.2
a(2)= 3.6
a(3)= .27
Note: B is a 3x3 Matrix of values in your sheet
How would I perform matrix multiplication of B * array "a"?
My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.
Thanks
I have a spreadsheet that shows a date, because of shipping I need to enter a
date and then calculate three months back and enter that date, For example
Cell A1 March, 2004 and I have to enter into cell B1 January 2004, how do I
get Excel to do this automatically.
Please can anyone help me answer any of these questions:
How would you add the contents of the cells from A1 to A10. Give 2 ways
to do this. (hint: Look up "Examples of common formulas" in Excel
help
How do you format a cell to have dollar signs next to the numbers?
How do you add the contents of cells A1 through A10 on sheet 1 and make
the answer show up on sheet 2?
How do you rename a sheet?
Can I delete the sheets that I don't need? If so how?
How do I freeze rows or columns of the sheet so that they don't'
move when you scroll up or down? Example: My name is in Column A, when
I scroll right my mane stays on the screen and column B moves behind A
How do you add up all the contents of column D and make the answer show
up in column C?
Thanks in advance!
abi
Hi there
I have many array formulas from Cell B2 to Cell E25000 in an Excel worksheet called results. These array formulas take on values based on another worksheet called STYLE-COLOUR. When I update the worksheet called STYLE-COLOUR the arrray formulas in the RESULTS sheet is taking around 20 minutes to calculate. Is there a way to shorten the time??
Mario
Hello everyone,
This is my first post, I am trying to build a quote templete for work that will take the lowest price quoted and plug that information into one column. I know it can be done because where I worked before we had a excel work sheet that did this. Can anyone point me in the right direction?
Sincerely,
Declare62
I went to the website cpearson.com and got the information for the syntax and the formula to sum numbers in different colors on a worksheet
now my problem is where do i put the visual basic part of the works so the formula can call on it
can you tell i have no idea but great hopes!!!
thanks everyone
For those who do not have a background in programming or mathematics, the expression Array may not be familiar.
So what exactly is an Array?
For our purposes, an Array is simply a set of values which can be stored in a formula, a range of cells, or the computers memory.
The size of an Array can range from two values to thousands.
There are several different types of Arrays used by Excel when working with formulas:
An Array stored in a Worksheet in a range of cells: For example, when the SUM function sums the values stored in a range of cells, it is treating those values as an Array.
Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an Array of values into the SUM function arguments: =SUM(1,2,3,4,5).
Or
Enter an Array enclosed in brackets into the formulas argument. For example, use the MATCH function to return the position of the number 10 in an Array of values: =MATCH(10,{3,7,10,15,20}). The result = 3
Formulas such as SUMPRODUCT utilize computer memory to store values temporarily while calculating complicated math problems. These values are stored in an Array.
To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the SUMPRODUCT formula stores each multiplication product in an Array (Array size is 3) and then adds the three values from the Array.
The SUMPRODUCT formula: =SUMPRODUCT(A1:A3,B1:B3),
Result - total sales=$260.
As explained in the previous section, many formulas create Arrays when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an Array/Arrays to store values.
Use the SUM function to return total sales (see previous example). The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result- total sales=$260.
Enter the formula, select the cell, press F2, and then simultaneously press Ctrl+Shift+Enter.
Hi,
I am trying to transfer the contents of an array into a range of Excel (red line in the code below). Though I am able to do that when the array has values, however, it is giving me an error when the array has formulas.
How can I get this to work?
Code:
Dim Vma As Variant
...
TotalRows = .Range("Spread").Count
Vma = Application.Transpose(.Range("Spread"))
For i = TotalRows To 1 Step -1
Vma(i) = "=RC[-2]- RC[-1])"
Next
.Range("Spread") = Application.WorksheetFunction.Transpose(Vma)
...
Thanks
MG.
Im using Excel 2007 and recently had to save the file as 2003. There was a problem with the array formulas. Does Excel 2003 not support array formulas?
Here is my formula:
{=SUM(IF(FREQUENCY(IF(LEN(Reps)>0,MATCH(Reps,Reps,0),""), IF(LEN(Reps)>0,MATCH(Reps,Reps,0),""))>0,1))}
*('Reps' is a named range)
** The formula, looks at a list acnd counts only the unique text entries.
Help - I recently changed to Excel 2007 and just noticed that a huge excel spreadsheet that I created 5 years ago and update every morning to reflect my companies sales pipeline is now replacing many of my array formulas in it with {=#N/A} when it didn't do this just a couple weeks ago? The strange thing is, it does not do this to all columns with array formula's, just some of them?
I am still saving it as an 2003 "xls" file and it was fine up until about 3 weeks ago. I thought it was something I did since then so I spent 5 hours this morning going back to the version I thought was ok and updating it to today... just to find out that when I saved it and went back in... the {=#N/A} error was back???
I searched online and found a thread of someone talking about this being a problem with Excel 2007... does anyone know if I can fix it?
