Product If Function For Conditional Range
Oct 30, 2013
Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:
Jul-12
6.59%
Aug-12
1.13%
Sep-12
2.61%
Oct-12
-3.56%
[code].....
I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.
View 3 Replies
ADVERTISEMENT
Sep 30, 2009
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
View 2 Replies
View Related
Sep 4, 2008
I'm trying to do:
Column A is full of daily consecutive dates, Columns B to H are full of numbers.
I'd like to create function that accepts 2 dates and a column reference as input like this:
function(date1, date2, columnnumber)
And as output it gives the product of all the numbers in that referenced column between the two dates.
View 9 Replies
View Related
Dec 24, 2008
I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.
So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.
The drop down list displays the products and can be added to by typing new product names on the other sheet.
What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
View 5 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Mar 3, 2009
Maybe I should say conditional product of conditional sum. Eitherway, the values in column J are summed if P is column R for the repective row.
View 4 Replies
View Related
May 27, 2014
What's the functions can figure out on the below conditions, For example :
PRODUCTSYEARQTY
AAA 2015 2
ABB 2011 3
AAA 2013 8
BBA 2011 6
AAA 2011 4
BCC 2014 1
Conditions:
1) looking for AAA product
2) sum the qty is before 2014
The answer is :12
View 5 Replies
View Related
Sep 11, 2009
I am using the following formula to calulate the compounded rate of return for a series of monthly returns: {=(PRODUCT(1+(b1:b100)/100)-1)*100}
I would like to be able to integrate a VLOOKUP function into the above formula so that it looks at the dates in column A and then apply the PRODUCT function above to the return values in column B that fall between start and end dates specified in cells C1 and D1.
View 4 Replies
View Related
Dec 8, 2009
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
View 3 Replies
View Related
Jan 3, 2010
I have a very large spreadsheet, a few columns and rows I attach..
in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.
What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down
see attached sheet
View 5 Replies
View Related
Aug 5, 2014
I created a large table with data (1700 lines long) and am looking to create a sheet that will use it to do a call for totals whilst filtering for a specific line.
This is what I have currently, filtered by delivery date and key ("furniture desc").
The 58 and 54 are totals for that particular product which I would like to ideally see on a separate sheet.
result1.JPG
result 1a.JPG
What I would like to achieve is this: result2.JPG
What kind of formula would I need to use to first filter by date, then by key, then to tally up the quantities and drop them on another sheet?
Bearing in mind that other keys are intertwined on the Y axis so I can't use =SUM(xx:xx)
View 2 Replies
View Related
Jan 30, 2014
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies
View Related
Feb 20, 2009
i need to look up a product based on the number and the qty. see the example attached.
On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.
View 4 Replies
View Related
Jul 16, 2006
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.
View 2 Replies
View Related
Jan 29, 2009
I have two columns of data. for example:
Column A
1
3
4
0
2
0
0
3
Column B
Product A
Product A
Product B
Product B
Product A
Product A
Product B
Product B
What I need the function to return is:
Product A - 3
Product B - 2
This is what I thought the function should look like but it is not working:
=IF(A6:A50>0,COUNTIF(A6:A50,"Product A"),0)
If Product A or B did not have any values >0, it would return a zero.
View 4 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
Feb 3, 2009
I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Dec 4, 2008
I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"
I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.
View 3 Replies
View Related
Feb 8, 2014
I would like to use conditional formatting to highlight the max value if the constraints are not violated.
For example,
Row 91 - Profit
Row 94 - Constraint A (750<=A<=855)
Row 95 - Constraint B (12<=B<=17)
So, I tried to type this in conditional formatting but it didn't work ><
=MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),$B$91:$Q$91,0))
View 9 Replies
View Related
Dec 14, 2009
Is it possible to use OR function in Conditional Formatting? I want to do the following.
If A1 = 1, A2 cell is filled in Red
If A1 = 2, A2 cell is filled in Red
If A1 = 3, A2 cell is filled in Green,
If A1 = 4, A2 cell is filled in Green,
I only managed to do the above for 3 conditions as Conditional Formatting only allows me to no more than 3.
View 6 Replies
View Related
Apr 12, 2013
I want to apply conditional formatting in a cell based on the contents of another cell. I can do it if I just want it based off 1 condition (=b151="sample") but would like to if it met 1 of a couple possibilities.
For example, cell H151 I would like to fill green if cell B151 contained the text "sample" or "example". B151 has a list box with about 10 possibilities but I only want a few of those to turn H151 green.
View 3 Replies
View Related
Jul 10, 2009
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
Eg.
Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756
This would need to return 28756.
View 5 Replies
View Related
Sep 8, 2006
Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.
View 2 Replies
View Related
Aug 25, 2007
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
View 7 Replies
View Related
Mar 27, 2009
I imagine this is a simple conditional SUMIF function. I'd like a cell to add values in e.g. column "d" when that row meets certain criterion in column "a".
In other words, I have a column that has times recorded in minutes, and another that says a person's name which correlates with the times. I'd like a cell on another sheet to give a total sum of minutes for each person.
Ideally, part of the function would translate the minute count into hours/minutes, but I think I can figure out how to do that by changing the format in the cell...
View 6 Replies
View Related
Nov 12, 2009
I am having many difficulties getting conditional formatting using the match function to work correctly in my excel document.
What I would like to accomplish is as follows in the 3Q09 tab. If a subdivision name is found in C-62 through C-70 and a match is found for the subdivision name in AB-24 to AB-66 I would like it so the AB-24 to AB-66 Subdivision name is bolded for every match. I removed the function formulas from the 3q09 tab I used to have since they didnt work and I do not want to confuse anyone.
This is correctly done on the "Working Correctly" Tab included in the same file, so please view this for clarification if you need it. Why it works on one tab and not the other simply baffles me.
View 6 Replies
View Related
Oct 5, 2013
I have three columns named "Type" , "start_date" and "end_date".
Type column can hold only "A","B" and "C".
start_date and end_date columns are time stamps. ex: 9/14/13 9:35 AM
I want to do conditional formatting like,
If type = "A" and (end_date - start_date) value between 30 and 40 (in hrs) then it should be amber.
I did for greater than and lesser than values, but I am facing problem while doing the above one.
View 3 Replies
View Related
May 9, 2006
for my thesis i need to create median industry multipliers. i have a list of
companies with their industry codes and multiples, but now i need to create a
list with medians per industry. is there a function similar to SUMIF for
medians?
View 11 Replies
View Related
Sep 24, 2008
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).
View 2 Replies
View Related
