Cost Average Effect

Oct 19, 2006

Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms

Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.

How big will the difference be between example 1 and 2 ?

*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.

Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.

Sum The Total And Find The Average Cost

Oct 1, 2008

I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price (\$) together and divide by the sum of A&B.

In other words find the average price for the total of each item..

A B C
Code Qty \$
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2

Inventory: FIFO, LIFO And Average Cost

Oct 14, 2005

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

Carousel Effect

Jul 30, 2009

I would like to produce a carousel effect. i will start with a basic example and then explain the complications.

Basically I have a range of cells of indeterminant length. The left column is numbered vertically - let's say 1,2,3,4,5,6. I would like to be able to generate a carousel effect so that if I right click on a number all the cells below move down and the number at the bottom fills the gap so that if I right click on 1 the cells would become 6,1,2,3,4,5 or if I right click on 3 the cells would become 1,2,6,3,4,5.

The complication is that there will be more than one of each number eg 1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5,6,6,6,6. (there will allways be the same number of each) so I would want the whole block to move down and the ones at the bottom to come up and fill the gap.

The other slight complication is that the range of cells is 5 columns wide so I would want the whole block of cells to carousel.

How To Add Press Effect For A Button

Oct 10, 2012

I have added a shape to my spreadsheet to work as a button. All is fine and it has the desired functionality however, adding the actual effect or simulate the effect of a button click/pressed?

VBA / Function To Calculate Effect On Inventory?

Jul 6, 2014

I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.

It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.

I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.

I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.

Can't Get Tick Boxes To Effect Pie Chart

Apr 28, 2014

I have set up a spread sheet which uses tick boxes and I have calculated it all up so that the percentage of ticks ticked is displayed in a cell etc. and when I link that data to a bar chart it works perfectly. .e.g as I tick the boxes the bar chart increases

BUT when I try to do the exact same thing with a pie chart it doesn't work.

Formula: Desired Effect Is Under Column 3

Feb 17, 2010

Not sure what formula to use for this set-up:

Col1 - Name_Last
Col2 - Name_First
Col3 - Ref_Name

The desired effect is under column 3, you have "Name_First Name_Last". Attached sample file.

Scroll Bars That Effect Active Row

Jan 29, 2013

I am trying to make a spreadsheet that contains numbers in columns D to P. My end user wants to be able to adjust these with scroll bars so I could make ActiveX scroll bars under each number in each column but this would be a lot of scroll bars (one in each row and columns)!!!!

Is there are way where I have have scroll bars in column D to P that only effect the active rows and always sits below the active row?

Cool Colour Fill Effect

Sep 12, 2013

I want to gradually change th colour in a block of cells. my plan is to use code to sequentially fade through ten or so shades from light to dark. is there a way to slow the code down so that it takes a couple of seconds to run the sequence?

Do Piviot Table Effect VB Code?

Dec 11, 2006

I created a couple of piviot tables from the same sheet I had VB code in. Since the creation of these P tables the code has stopped functioning.
In a attempt to re-activate the code i moved the piviots to a new spreadsheet. But the code is still not working....

Invert The Effect Of Freeze Panes

Jun 13, 2007

I would like to freeze the bottom part of my spreadsheet, such that the user can scroll through the upper part but still be able to see the bottom part. The freeze command freezes what is above the line, I want to freeze what is below the line.

Nested IF Formula That Takes Into Effect Many Variables

May 5, 2014

Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...

1.5 O.D. - 16GA - 304SS = \$X

but

1.5 O.D. - 18 GA - 304SS = \$Y

and such.

The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.

O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.

Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.

=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!\$DD\$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!\$DD\$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!\$DD\$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!\$DD\$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!\$DD\$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!\$DD\$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......

Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?

Macro To Effect Cell Based On Column

Nov 26, 2008

I wasnt sure how to name this problem, but here goes.

How To Recreate ZigZag Effect On Stock Data

Sep 4, 2007

I look at stock charts that allow an overlay of "zigzag patterns." This zigzag links price extremes with a line. The price extremes are defined according to "wave size" which is described as follows:

"Wave Size: The Most Important Setting... and the thing you will want to tinker with -- is Wave Size. Wave Size is a number that defines a high or low price extreme, by stating the number of surrounding bars on either side that must not be higher or lower than the candidate extreme. Another way to think of it is: Wave Size + 1 is the minimum distance between extremes of the same high or low type."

Here is an example of what a stock chart with these zigzags drawn in looks like. [Click on the image to zoom in and see it better.]

[URL]

The black diagonal line is built using a "wave size = 5" using the above definition. And the green one is, "wave size = 20"

I am trying to build this into a spreadsheet so I can play with the data. If I have four columns: (a:open)/(b:high)/(c:low)/(d:close) I cannot figure out how to build the columns after to do this zigzag. I understand what the line is, but it just doesn't make sense to me how to derive it from the data. how to get a column n that has the value of the zigzag (with wave size = 5 for example) I

Excel 2010 :: VBA To Set Fill Effect In A Cell

Jan 5, 2012

Here's a problem I'd like to be able to solve in VBA:

Retrieve the current cell color (let's say A1 = 'green')

Format A1's cell colors (using the VBA equivalent of the 'Format Cells' / 'Fill' / 'Fill Effects' / '2 color Gradient' feature) so that the background cell color (color 2) is set to 'green' with color 1 being set to 'white' with a shading style of "From Center"

I'm using Excel 2010.

Limit Macro To Only Effect Open Worksheet?

Oct 26, 2013

Is there any setting or technique to limit a macro to only effect the open sheet in the workbook that it runs from?

Keydown To Effect To All Controls That Support Event

Dec 31, 2006

Is there a way to fix a private sub to catch keydown effect of various combos and textboxes of my userform?

Instead of typing the same thing to each and every control's keydown event just call a sub. Code goes like:

Select Case KeyCode
Case 112
ShellExecute 0, "open", "hh.exe", ThisWorkbook.Path & "1.chm", "", 1
Case 113
If MsgBox("Backup now", vbQuestion + vbYesNo) = vbYes Then BackUp
Case 114
Col = 3
SearchD
Case 115
showexcel
Case 116
e.c.t.

Instead of that, if i place code in a private sub test() just call test in keydown event

Graph To Illustrate Desired Effect Of Marketing Campaign

Jan 21, 2014

Need a graph to illustrate the desired effect of the marketing campaign as a shift in market equilibrium with reference to price and quantity adjustments.

Create Macro To Add Fill Effect On The Bars Of Chart

Jan 24, 2012

I'm trying to create a macro to add a fill effect on the bars of my chart.

However, I don't want it to change the bar's current color. Just to do the effect on the current color.

Code:
Sub fill()
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With

[code].....

Hyperlinks :: This Operation Has Been Canceled Due To Restrictions In Effect On This Computer

Mar 6, 2010

while trying to view a hyperlink i just created in a cell within a worksheet, a message popped up saying:

mircosoft office excell:

Charts: Vary Colors By Point With Area Fill Effect

Oct 30, 2006

I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series

So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?

Calculating Cost Per Second

Oct 25, 2007

I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.

Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.

To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.

Calculating Cost

Apr 17, 2009

Problem - billing spreadsheet for prisoner fee.

1 - 8 hrs = \$55
9 - 24 hrs = \$55 + \$65 or \$120
Anything over 24 hrs - \$65 for each additional (24 hrs) (\$185)

So if you were locked up for 6 hrs it is \$55. If you were locked up for 18 hrs it is \$120. If you were locked up for 28 hrs it is \$185. And if you were locked up for 49 hrs it is \$250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.

Cost Per Row Of Data

Mar 6, 2014

I have an excel table that among other fields it has a [product Charge] field.. what im trying to do is to get a cost per row based on the [Product Charge] field.. so example if I have 10,000 rows and the total for that field is 1,000,000 the cost should be 100 ( [Product Charge] / total rows).... I then want to be able to use it in a pivot table...

Least Cost Routing

Aug 3, 2009

im working on a VoIP company, and having a hard time to figure out a result on a little time.

the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns:

I am including an image, sorry if the image seems to be heavy and large:

as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers.

While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate.

Red marks=are not suitable or non-profitable buy rate for the sale rate.

Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.

Cost Vs Units Graph

Jan 25, 2009

I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):

Cost per unit for 0-10 units: \$1
Cost per unit for 11-20 units: \$3
Cost per unit for 21-30 units: \$5
Cost per unit for 31-40 units: \$8
Fixed cost: \$100 (this is regardless of the number of units produced)

Basically I'm looking to construct a cost vs units graph.

Calculating Cost Per Month?

May 8, 2014

I am trying to create vba macro that allows me to calculate cost per month.

I have created a basic table with two columns

Cost
Month

200
Jan

300
March

150
Jan

470
April

150
March

I have another sheet , that has two columns

Month - Cost
Jan
Feb
March
April

I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.

So far logic , that I have came up with is.

I create a For loop statement that looks column and sort each month into an array.

Than the second loop will use the array to find the cost with the allocated array value to sum it.

Transferring Cost From One Sheet To Another

Feb 24, 2009

I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?