Diagonal Sum Product If Formula?

Aug 31, 2012

I am stumped on coming up with a dynamic formula that calculates a value based on an increasing number of "sums of products". Specifically, I need to calculate the total number of repeat users based on a "decaying' repeat rate. In other words, I need it to calculate = Sum[... +(d5*f5)+(d4*g4)+(d3*h3)]

or in equation form:

where
n = (1, "infinity")
and
i = {1, 2, ... (n-1)}

with
"Xn" is the calculated value of repeat users for month "n" (output/result cell)
"mi" is the number of new users (in month i)
"Nn-i" is the column 'number' of the repeat rate value (%) multiplier

Here is a screenshot of the first couple rows to illustrate:

I actually have about 70 months of data (rows) and roughly the same amount of columns representing a decaying repeat rate (70 or so) and these will be increasing as time goes on so I need this formula to be dynamic (i.e. - copy it down the column of (# of repeat users)

I am using Excel for Mac 2011.

View 9 Replies


ADVERTISEMENT

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

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

Dynamic Named Range For The Product Name On The Product Details Sheet

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

Macro For Name Generation From Product To Product With Sizes And Filenames

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

Sum The Product Formula

Mar 26, 2008

I have a Sumproduct formula that should be resulting in 0. However, it is resulting in a very small number. I have 2 columns of data. The first one contains -169.5, -123.4, -34, -67.4, 169.5, 123.4, 34, and 67.4. The second column contains all 1's. When I create the following formula, =SUMPRODUCT(H27:H35,J27:J35), it results in 5.68434E-14.

Sometimes, when I have different numbers in the first column, but it still adds up to 0, the Sumproduct works.

View 12 Replies View Related

Sum Product Formula

Aug 29, 2008

What I need to do is count the number of cases and values of everything written between the first of January and today (242).

I know how to do it by counting the days from the 1st of Jan to today but how can I do it so it automatically updates each day.

This is what I have at the moment.

The product name is in K, the date is in H and the value is in M. The range is from cell 7 to cell 94


=SUMPRODUCT(--($K$7:$K$94="Pension"),--($H$7:$H$94<=TODAY()),--($H$7:$H$94>=TODAY()-242),$M$7:$M$94)

View 9 Replies View Related

Sum Product Formula With Criteria

Jan 27, 2009

The following formula takes a census of people in our area by date and by hour (sample data attached): =SUMPRODUCT(--(FLOOR(Data!$B$4:$B$39,1/24)<=$B2+C$1)*(Data!$C$4:$C$39>=$B2+C$1))

The formula works great, but I would like to add another component whereby the census will only count someone if they have a certain description (see column A - with color descriptions). So how can I setup the formula, to say, only look at the "red" colors during the same specified dates/times and give me the same type of census...instead of looking at all of the data or creating 50 different ranges for all the colors?

View 2 Replies View Related

Sumif/product Formula

Sep 29, 2009

I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B.

[code]WORKSHEET A WORKSHEET B
A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2
B BLANK BLANK 10 BLANK DATE:1/28/09
C NAME DATE WORKED 12 NAME
D Jones 1/2/09 13 Bell
E Smith 1/6/09 12 Smith
F Cox 1/28/09 12 Cox
G Cox 1/28/09 10 Collins
H Collins 1/28/09 11 Jones

In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH

I've spent hours tyring to get this thing done!

View 11 Replies View Related

Sum Product Formula Between Two Dates

Jul 18, 2014

I currently have a formula that totals a product between two dates, however I need to add up a reason code for that product also in a different tab. My current formula is

=SUMPRODUCT(--('Product'!$A$2:$A$1069=B6), --('Product'!$B$2:$B$1069=Data!$A$1))

I want to add another section so that it looks at the dates between C6 & B6 and also the product in Data A1 but totals the reason from cell C10 in the data worksheet.

View 1 Replies View Related

Create One Formula That Will Take The Product Of Numbers?

Mar 10, 2014

See attached spreadsheet. I am trying to create one formula that will take the product of numbers in column B based on criteria in column A (want the product of %s in each year). Product if formula.xlsx

View 7 Replies View Related

Product Look Up: Look Up A Product Based On The Number And The Qty

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

Diagonal SUM

Mar 15, 2008

I am looking for a simple non ARRAY formula (OFFSET)? that will use range A1:E5 to create a new range which will include cells A1, B2, C3, D4, E5 (the diagonal). The sum of this formula would give: 30 ...

View 9 Replies View Related

One Array Formula To Sum Product With Weighting Data

Jun 11, 2014

My problem is a little more complex than sumproduct.

I have a ratings column and exposure column. I want to weight those ratings before doing a sumproduct, at the same time ignoring #value.

In the attached file, I want to achieve the green cell in one go, based on the orange raw data.

Attached File : Weight Data.xlsx‎

View 2 Replies View Related

Formula - Week Product Level Count

Jan 5, 2014

How to solve my problem in attached file : Week Product Level Count.xlsx

View 1 Replies View Related

Formula Needed That Identifies Last Time Product Run

Jan 21, 2014

Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.

In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.

I have attached an example : example.xlsm‎

View 2 Replies View Related

If And Vlookup Formula (bring Up The Corresponding Product Description )

Jun 15, 2009

I'm trying to get a cell {in the attached spreadsheet 'Order Form' Sheet- Cell 'B13'}, to bring up the corresponding product description {'Products' Sheet- Column C}. For the cell that is to bring up the product description {'Order Form' Sheet- Cell 'C13'}, the following is the formula I'm trying to use, but is coming up "#N/A":
=IF(C13>1000,VLOOKUP(A13,Products!$A$2:$D$12,3),IF(C13>287,VLOOKUP(A13,Products!$A$14:$D$25,3),IF(C1 3>0,VLOOKUP(A13,Products!$A$28:$D$38,3)," "))).

I haven't attempted the "Cost/Bottle" formula yet {'Order Form' Sheet- Cell 'D13'}, but it needs to correspond the product number {'Order Form' Sheet- Cell 'A13'} and the quantity ordered {'Order Form' Sheet- Cell 'C13'}, with the cost/bottle on the that quantity found on 'Products' Sheet, Column D.

View 5 Replies View Related

Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

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

Sumproduct Formula: Count The Components Used To Make One Product

Oct 7, 2008

Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

View 14 Replies View Related

Summing Along A Diagonal?

May 30, 2002

is there any easy way to take the sum of a really lond diagonal? I know i could use a macro, but I was wondering if there was a way to do it with formulas, the sums will ultimately be used for a graph,

View 9 Replies View Related

Special Diagonal Sum

Feb 23, 2009

Without VBA, how do I sum diagonals such as N2 and N3, but also like N7 where the values are limited to row 13? Hopefully, the post illustrates better than my words...

View 9 Replies View Related

Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code

Apr 18, 2008

I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors

Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"

When I record a macro it gives me this, but again, I need the last cell in terms of "T"

ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"

View 3 Replies View Related

Calculate The Sum Of Rows/columns/diagonal

Jun 2, 2009

I have a table with changing length of the rows and columns. I need to calculate the sums of the rows, the columns ad the diagonal. I managed to write a working code for the columns, but have no success with the rest. Here is what I have:

View 4 Replies View Related

Diagonal Values Into A Single Column

Feb 15, 2007

Does anyone know of a macro that can take values in a daigonal and put them in a single column?

Like for example... if you refer to the attached image, I basically would like to select all the diagonal values in red then put them all in a single column.

View 7 Replies View Related

VLOOKUP Referencing The Diagonal Cell Instead Of The Horizontal

Sep 4, 2013

I am trying to get my formula to reference directly to the right of the look up value but it keeps returning the diagonal value that is located to the right and up one cell.

excel 1.jpg
excel 2.jpg

View 14 Replies View Related

Add Diagonal Borders To Cell On Double Click

Sep 14, 2007

I have made a toggle button to turn diagonal borders on and off in a single cell. Is there a way to make the button transparent, such that the button can be placed over the cell itself and the contents of the cell will be visible underneath, no matter the condition of the toggle?

View 8 Replies View Related

Paste Text From Column Into Diagonal Line Of Cells?

Jul 8, 2005

How do I paste text from a column into a diagonal line of cells (i.e. A1, B2, C3, etc.)? Additionally (and this may be related), how do I get the cursor to move to a diagonal cell after an entry? (as opposed to down or to the side)

View 2 Replies View Related

Table Presentation: Diagonal Line Splitting Two Values In One Column

Mar 4, 2007

I have a table with data representing $income vs %Debt burden respectively. The data on the table per respective intersection provides number of accounts and total ($)value of accounts. I have tried to combine the two pieces of information into one column and have a diagonal line separating the two numbers. (sample table attached).

View 2 Replies View Related

Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

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

Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product

Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

View 2 Replies View Related

Formula To Make Product Of Two Negative Numbers Negative

May 12, 2009

I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved