# Formula To Multiply Single Value By Array Then Sum?

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
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
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
Jun 16, 2009

To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.

View 4 Replies
View Related
Feb 9, 2010

I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.

The table data is very simple, and can be in just two columns:

1, value

2, value

3, value

4, value

5, value

Where 1 thru 5 are time periods, and values are various numbers.

I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be

=Gammadist(5,x,y,TRUE) (x,y values not important here)

and the formula for the second value would be:

=Gammadist(4,x,y,TRUE)

The only thing that changes is the period number.

So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)

I can get this far:

={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}

But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.

View 9 Replies
View Related
Mar 28, 2014

I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.

I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.

I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.

View 3 Replies
View Related
Jun 27, 2014

I am looking for formula , single cell two numerical values , multiply in different columns

example :

Cell (A1) contain: =350,23+25,59 (two values in a single cell)

request:

in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number

View 2 Replies
View Related
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
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
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
Aug 22, 2014

So I have the first part of my equation and it works fine. The equation is in Column D.

=IF(ISERROR(FIND("Van",A7,1)),"",B7*C7+50)

What I want it to do is: Combine the above for Col D with: if the Unit Name Column (A) says anything EXCEPT Van, I want it to multiple Cost (Col B) by QTY (E) and put the total in Col D.

View 7 Replies
View Related
Aug 8, 2006

All to occur in cell G2

Condition 1.

If F2 is > 0 then multiply F2 by D2.

Condition 2 in the same cell.

If the product of F2 multiplied by D2 is > E2 AND the sum of F2 and F1

multiplied by D2 is > 0, then add the product of D2 multiplied by the sum of

F1 and F2 to E2, otherwise input E2

Condition 3 in the same cell.

If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1

multiplied by D2 is > 0, then input the product of D2 multiplied by the sum

of F1 and F2.

Condition 4 in the same cell.

If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0, then 0.

This is the best way I could think of the write out my intention. I think if

I can get the above scenario into a one cell formula that should resolve my

pain.

View 11 Replies
View Related
Aug 9, 2012

I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:

The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level

0 = Top level

1 = Level Below

2 = Level below that etc, etc

If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity

THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number

if the quantity fields are blank, then always multipy below by 1

This needs to continue on down the chain of levels

So basically you end up with the correct quantites of each item at each BOM level.

View 9 Replies
View Related
Aug 11, 2013

I have Excel 2010. I am creating a spreadsheet for server backups. I have always been told to take the current size of the disk and multiply it by 10% to figure out the backup size needed for the backup. I am trying to create a formula to do this. I have a server with 25GB. The formula i tried was

=a1*10%. A1 being 25gb. Excel does not like the GB in the cell also this is only giving me the difference. I need to know what size to allow for my backup on this server.

View 9 Replies
View Related
Jul 30, 2014

I am having trouble with the final formula for this spreadsheet.

I want to be able to select "Holiday/Sunday" from the drop down men in column H, and have it multiply the total in column F by 2. There is already a formula in column F that sums column E-D.

View 4 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
Mar 10, 2009

What I’m trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. I’ve attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}

But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.

View 5 Replies
View Related
Jun 16, 2009

Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

View 2 Replies
View Related
Aug 16, 2012

Ok I'm writing an extraction formula. I've got my array of Trues and Falses, but now I need to go through each row and check if all the columns are TRUE, and then return the row if all the columns are true.

Right now I'm just working with a dummy set of data... my real table is some 50 columns wide and over 300 rows, but this gives you the idea of what I'm doing:

I tried putting in a Countif and using it like and array to count the different rows and return the number of times each row was returned... but countif goes nuts when you try an array for the criteria.

Formula:

=IF(IFERROR(($H$2:$J$13/B$1:D$1)>=1,TRUE),1,0)*IF(IFERROR(($H$2:$J$13/$B$2:$D$2)

View 8 Replies
View Related
Jun 20, 2008

How can you test that no single element of an array is equal to a particular value?

View 9 Replies
View Related
Jul 16, 2007

I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand. Here is my formulae: =SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10)

ABCD

1ValueQtyProduct

2299.944PXT

3186.53PXT

4711.071PCA

5561.862PCT

6608.961PXT

7520.026PCT

8427.682PCA

9397.341PCA

10387.664PCT

View 2 Replies
View Related
Feb 25, 2009

This has been kicking my can all morning! Should be simple. I'm trying to conditionally format an array from the value of a single cell.

=IF(A1<80, A3:A24,RED,0)

=IF(A1>80,A3:A24,GREEN,0)

=IF(A1>120,A3:A24,BLUE,0)

View 2 Replies
View Related
Jul 13, 2012

There is a method to use ROW(1:10) within an array formula to strip a string into individual elements e.g.

HELLO > {"H","E","L","L","O"}

I cannot find it anywhere, thought it was something like =MID(A1,ROW(1:10),1) but not yielding results.

View 4 Replies
View Related
Sep 10, 2012

I'm trying to create a single array from multiple ranges... I'm not sure what syntax to use:

Code:

Dim dat4() As Variant

Set r = Sheet13.Range("rsqlassetid")

Set r2 = Sheet13.Range("rsqlparentcat")

dat4() = (r , r2)

I can create an array with multiple columns from a range if the columns are next to each other but in this instance they're not.

These 2 ranges both have the same number of rows and I'm trying to combine them into a 2 column array, but not sure how to make it work without looping, rediming the array and using a secondary array to preserve the data...

View 9 Replies
View Related
Jan 17, 2014

I had some number combinations as this 5-23-34-233, 50-233-34-45, 34-5-23-45-67. The length can be variable in column A.

I want a formula which should return array of numbers of each cell in column A. So if A1 has 5-23-34-233 so the formula should return array like {5,23,34,233} and like wise.

View 8 Replies
View Related
Jan 3, 2008

I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.

Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant

Dim vHolder As Variant

Dim vArray As Variant

Dim lElementCount As Long

lElementCount = 0

For Each vHolder In dataToConvert

lElementCount = lElementCount + 1

Redim vArray(1 To lElementCount)

vArray(lElementCount) = vHolder

Next vHolder

CreateSingleDimensionArray = vArray

End Function

View 4 Replies
View Related
Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies
View Related
Jan 3, 2012

I am trying to rank records from multiple sections of a single array. Since there are over 100 sections I want to use a formula. Using the MATCH function I have the first row for each section of the array (Col D below). The psuedo code of what I'm trying to do is:

RANK(B1,Bfirst row of section:Bfirst row of next section -1)

Example (results in Column C):

A B C D

1 West 100 3 1

2 West 150 1 4

3 West 125 2

4 East 50 3

5 East 75 2

5 East 140 1

I will also need to increment the section as I complete each section of the array. Am I trying to do too much in a single cell??

View 1 Replies
View Related
Jul 2, 2014

I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.

I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:

To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).

I already have it working for returning a single value with the following formula but need it to return multiple values.

=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,

MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP

(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")

View 1 Replies
View Related
May 30, 2006

I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.

View 9 Replies
View Related