Multi Factor Weighting To Delta

Feb 16, 2008

I have two factors that both change, resulting in two unique values. I'm trying to determine which portion of the resulting delta is attributable to each factor. Example:

Hourly Cost Run Rate Piece Cost (Hourly Cost / Run Rate)
$105.00 4,500 $0.0233
$126.10 3,000 $0.0420

Two unique results titled Piece Cost = Delta
$0.0420 - $0.0233 = $0.0187

How much of the $0.0187 is attributable to the change in Hourly Cost and how much of the $0.0187 is attributable to the change in Run Rate?

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Sumproduct - Calculating Dollar Weighting With One Isolation Factor

Apr 30, 2013

Using excel 2010: I am calculating a dollar weighting with one isolation factor, the state where the sale took place. My current formula is below. Each dollar amount in AG3:AG2000 has a corresponding weighting in AB3:AB2000. The results are isolated by the corresponding state in the range E3:E2000.

I want to add in an additional isolation, the month when the sale took place. The months are located in H3:H2000. Where in my current formula would I insert the isolation for the month?

=SUMPRODUCT(--(Sales!$E$3:$E$2000='2013 NB'!S8),
Sales!$AG$3:$AG$2000,Sales!$AB$3:$AB$2000)/
SUMIF(Sales!$E$3:$E$2000,'2013 NB'!S8,Sales!$AG$3:$AG$2000)

View 9 Replies View Related

How To Return Delta Of Dates

Nov 22, 2011

I have a sheet where I need to return the delta of dates as follows:

IF C has a date then I need the delta between C and A UNLESS there is a date in B in which case I'd need the delta between C and B.

If C is blank then I need then I need the delta between "today" and A UNLESS there is a date in B in which case I'd need the delta between "today" and B.

View 9 Replies View Related

Insert Greek Delta Symbol Into Header Using VBA

Sep 24, 2012

I'm trying to figure out how to insert the delta symbol into a header using VBA. The symbol code is 0394. My line of code is the following but it is giving me a weird "D"-like symbol. Any thoughts on how to correct this?

Code:

ActiveWorkbook.Sheets("The Flux").Range("G2") = ChrW(394) & " Prev. Year"

View 2 Replies View Related

Pivot Table Fields To Calculate Delta Between Two Proceeding Columns

May 5, 2014

I have a Pivot Table. I have two Columns Currently Current Week and Previous Week. The Values are displayed as a count for the Status Field. I would like to know if it is possible to add a calculated formula that will take the count difference between Current Week and Previous Week?

View 1 Replies View Related

Data Manipulation And Weighting

May 25, 2009

The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are.
IE this is an example
Call Types Call Lengths (minutes)
BA 1
BA 1
BA 3
BA 2
at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.

View 11 Replies View Related

Inverse Distance Weighting Function?

Apr 15, 2014

I have longitude/longitude points in which I would like to apply inverse distance weighting statistics to.

I would like to possibly create a chart with the results...

See link below for explanation of inverse distance weighting.

[URL] ....

is there a function in excel or access that I would be able to use to achieve this goal?

View 3 Replies View Related

Weighting Random Numbers In Aggregate

Dec 8, 2008

I have column E listing all of the favorites and column G the underdogs, each row represents one game, there are 34 all together. I want a formula to pick the team based on a random generation. That part i have....

=choose(randbetween(1,2),e7,g7)

but here is the rub.

I only want the total number of underdogs to be 20% and the favorites to be 80% of all of the picks.

I am not sure it can be done as each formula must look to what the other formulas are doing...aside from it being circular i think it just impossible or i am not as advance as some of you.

View 14 Replies View Related

Nested If Factor

Apr 17, 2007

I have the task of creating a spreadsheet that will track and give point value to our sales reps' daily accessory sales. The following is the point value criteria for dollars:

Sell over $30 - 1 point daily
Sell over $100 - 2 points daily
Sell over $150 - 3 points daily
Sell over $300 - 5 points daily
Sell over $500 - 6 points daily
Sell over $1,000 - 7 points daily

Each cell will have a different dollar value in it. I've tried so many different ways to do this IF factor that I have confused myself and spun in circles. Can anyone help me with this? I am about ready to pull out my hair, and really don't want to have to go in and manually do this daily for 30 reps.

View 5 Replies View Related

Rounding Up By A Factor

May 22, 2007

I am having a problem getting a number to round correctly in Excel. I know I can use =ROUNDUP(x,x) to get it to the nearest whole number but I also need the rounded number to be a factor of another number like you would use in =MROUND(x,x). The problem is the MROUND function also rounds down. As an example if I have the number 12.5 and I want this to be rounded up by a factor or 4 I would get 16.

View 8 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

Predict Value Based On Weighting Of Prior Months

Dec 28, 2011

I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.

Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?

View 5 Replies View Related

Using A Percentage Weighting To Create An Average Score

May 26, 2009

I have 6 different sections of questions, each section having a final average score between 1 and 5.

I would like to somehow create a percentage weighting on each of these sections and then have a final score accross all section, taking into account this weighting?

View 9 Replies View Related

Return Row Index Of Selected Items In Multi Column Multi Select Listbox

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

Copy Values Down By Given Factor

Feb 3, 2014

See attached file. I am looking for a macro which can copy/paste down a value (column A) by a given factor (column B). The result should be a list of all values (column C). The amount of values and factors is unknown (the attached example ends at row 5).

copy.xlsb‎

View 4 Replies View Related

Formula For Present Value Factor

May 22, 2007

Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.

View 9 Replies View Related

Smallest Prime Factor Of A Number

Apr 14, 2009

Is there any formula witch i can use to find the smallest prime factor of a number?

Example: In A1 i write 30 (30=2*3*5 (the prime factors))

in B1 i write a formula, and the result will be 2 (smallest prime factor)

What is the formula?

View 7 Replies View Related

Returning Nth Factor Of Number Via Formula?

Oct 15, 2013

Is there a way of returning the nth factor of a number via a formula?

That is to say, if I have 10, its divisors are 1, 2, 5 and 10, and if I wanted to return the second factor, it would be 2.

View 9 Replies View Related

Random Selection Of Cell Value From List Based On Changing Weighting

Apr 24, 2009

I have a list similar to the below:

#....|Name.... | Weighting %
1Artur Boruc 1
2Igor Tudor 5
3Alessandro Grandoni 3
4Gian Elia Amoretti 3
5Olivier Kapo 12
6Kim Källström 13
7Emiliano Bigica 13
8Julio Baldivieso 12
9Fabrizio Miccoli 13
10Peter Vougt 14
11Massimiliano Esposito 12

What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...

I have used a formula such as

PHP
=INDEX(A$1:A$11,INT((RAND()*11)+1),1) 

to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?

An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.

View 11 Replies View Related

How To Sum Values Each One Multiplied By A Factor Obtained Using Lookup

Jul 15, 2014

I got a list of products and a price list. How to obtain a total for a given subset of products using a formula? Kind of sumif multiplied by a vlookup...

View 4 Replies View Related

Randomly Group N Elements (where N Is Factor Of 7) Into Groups Of 7

Oct 7, 2013

I want to group N elements which is a factor of 7 into groups of 3 randomly. For example, I have 21 elements:

ZSW
PMH
NYV
JTX
DEF
CRK
AOI

I want to group them into 6 more sets. Now, all in all 7 sets. Group them into other sets without the element repeating in the row it has occupied before. For example, for letter P. "P" has occupied row 2 in set 1, it cannot occupy row 2 anymore in other sets. Therefore, each element must have occupied row 1-7 in all the sets.

See attached excel file for the example output : grouping7-final.xlsx‎

View 14 Replies View Related

Macro To Spread Amount Over Multiple Months Using Working Days As Weighting

May 5, 2014

Phasing Design.xlsx

We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).

We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.

So far i have the following code:

VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long

[Code] .....

View 2 Replies View Related

1 Column Into Multi-columns And Multi-rows

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

Concatenate Multi-row Cells Into One Multi-row Cell

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

Calculating Adjusted Factor Based On Variable Values

Feb 7, 2014

Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)

How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.

Table_factors.jpg

View 4 Replies View Related

Conditionally Importing Data From One Of Several Different Worksheets Based On 1 Factor?

May 22, 2014

I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.

All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).

What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?

View 1 Replies View Related

Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time

Mar 3, 2009

I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!

View 4 Replies View Related

Multi Option Sum

Jan 8, 2010

I want to sum data in COLUMN C that is related to COLUMN B if the COLUMN F is blank.

View 10 Replies View Related

Multi If Count

Dec 7, 2008

I have to have to have 2 criterias for it to pass before the items are counted

I have 2 columns of data.
1 being a list of clients names, and 1 being if the Connection/Abandoned

Client Column is colum "F"
Connection/Abandoned is colum "J"

I need to count how many Connected and how many abandoned for each client

So if "F" equals "BPS" and "J" equals "Connected", then count.

View 13 Replies View Related

Multi Choice Sum

Feb 4, 2009

I have a spreadsheet which have data auto inported. (thanks to previous help on here). I have now come up against the next problem.

I wish to make the mark up variable depending on 5 options (workings I13;I17)

the choice is selected in column b after the data has been inported

then down on Rows 75 - 79 i have the totals of my choices.

Unfortunatly i do not know how to do a variable sum

I have attached a copy of the workbook.

View 6 Replies View Related







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