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
ADVERTISEMENT
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
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
Apr 18, 2007
I have two worksheets, one is setup with a part number only and the months Jan-Dec. The other worksheet has those same part numbers, but it also has the quantity and the months Jan-Dec. I need to do a lookup to find the part number, multiply it by the quantity on that given line, and then multiply it by the quantity in the month. I will show you how my worksheets are setup:
Worksheet1:
Qty P/N Jan
1 123456 100
1 123456 100
2 456789 50
4 123456 25
3 345678 500
1 456789 75
Worksheet2:
P/N Jan
123456 Sum of ALL Jan Demand * the Quantity of each item
345678 Sum of ALL Jan Demand * the Quantity of each item
456789 Sum of ALL Jan Demand * the Quantity of each item
View 9 Replies
View Related
Jul 17, 2009
Please see the attached .xls.
I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.
Basically there is a 2 dimensional array of weights (range1, limits)
each range in range1 and "limits" has a midpoint
a constant value (Fixed Amt) is added to each midpoint in range1
then multiplied by another constant value (Inflation Index)
The value is then capped by corresponding value in the limits range
this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results)
and the sum is returned.
I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.
View 6 Replies
View Related
Jan 24, 2014
I have a workbook with several different worksheets which I am copying from the individual sheets and pasting into a summary sheet, and multiplying the numeric numbers by minus 1 to reverse the signs.
This is all working fine, but I now want to copy an additional worksheet called "Plan" and paste this into the same summary worksheet, however I do not want to reverse the signs for this data.
All of the other worksheets begin with "Total" in the worksheet name, and this is how I am specifying which sheets to copy. I can re-name the "Plan" sheet to "Total Plan" if that makes the logic easier, the import thing is that the signs are not reverse for this sheet.
Column Z of the summary worksheet "RDBMergeSheet" identifies which sheet the data was originally copied from, so could I somehow use this to say if column Z = Plan, skip the multiplication by minus 1?
My current code is this:
VB:
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
[Code]......
View 3 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
Dec 19, 2007
I setup a Vlookup formula (in sheet 1) to find the figure in column C (in sheet 2) based on the company name and branch name but the result is incorrect.
View 3 Replies
View Related
Apr 5, 2013
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
View 1 Replies
View Related
Feb 12, 2010
I have attached a sample of my data. What I am looking for is the best (most elegant solution) to my problem.
I am trying to create a macro where I have 2 sheets. Sheet 1 will contain my data, sheet 2 will contain a single column of criteria to search through sheet 1.
I would like for the macro when it finds that criteria, it will delete all rows of data that is tied to that ORDER_NUM
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2113510395CANADA EMPACT20100108IN445N3113510395CANADA EMPACT20100108IHT9534113510395CANADA EMPACT20100108ID981C5115436953CANADA EMPACT20100110IPMDMN6115436953CANADA EMPACT20100110IN246F7116292017CANADA EMPACT20100111IY1G0M8116411203CANADA EMPACT20100111IT571R9119169642CANADA EMPACT20100113I9M62C10119169642CANADA EMPACT20100113IT272C
For example, it my criteria was part N445N, I would like for it to delete all rows of data that is tied to that ORDER_NUM (113510395).
Desired Output
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2115436953CANADA EMPACT20100110IPMDMN3115436953CANADA EMPACT20100110IN246F4116292017CANADA EMPACT20100111IY1G0M5116411203CANADA EMPACT20100111IT571R6119169642CANADA EMPACT20100113I9M62C7119169642CANADA EMPACT20100113IT272C
View 9 Replies
View Related
Aug 22, 2009
I have previously been performing a multi level SUMIF of named ranges with a large data range.
This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.
Basically, i'd like to select a range of rows which will parse into the SUMIF formula.
I have solved this by referencing an external cell to create the range.
View 6 Replies
View Related
Aug 21, 2007
I am using the following piece of code to 'link' a value from a cell in one workbook into another.
ActiveCell.FormulaR1C1 ="='[filepath]Front Sheet FIT'!r7c4"
However, i have come across a cell which displays the result of a reference to another cell (something simple like =b32). Normally I would just link to b32, however, the macro has to be applied to a large number of documents and in some cases text has been specifically entered, while in others the reference has been used. Is there any way of using VBA to display either the result of a formula (if there is one) or text if this is the case?
View 3 Replies
View Related
Mar 20, 2014
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
View 3 Replies
View Related
Dec 20, 2013
I am using a list like this.
A
B
C
D
1
Teacher
Name
Color
Size
[Code] ....
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
Teacher
Red
Green
Blue
Size 3
[Code] ...........
View 5 Replies
View Related
Oct 27, 2012
Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
View 5 Replies
View Related
Dec 30, 2008
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
View 9 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
Nov 12, 2009
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
View 3 Replies
View Related
Jan 8, 2010
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies
View Related
Jan 23, 2012
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
View 3 Replies
View Related
Jun 30, 2014
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
View 5 Replies
View Related
Jul 16, 2013
Let's say I have one column of;
1
2
3
4
5
6
7
8
9
What is the most efficient way to change this into '3-columns & multi-rows' like this?:
1 2 3
4 5 6
7 8 9
The actual list is a lot longer and numbers are not in order.
View 7 Replies
View Related
Aug 21, 2013
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
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
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
