# Value By Summing Volume & Multiplying With Price

May 6, 2008What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?

View 4 RepliesWhat is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?

View 4 RepliesADVERTISEMENT

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...

B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))

J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.

I couldn't find a lot of info but I also may not know where to look.

example:

if (b2<62) then price is ($sheet2.b2)

if (b2<124) then price is ($sheet2.c2)

if (b2<372) then price is ($sheet2.d2)

Is this even possible to put into a formula?

1 TO 10 25.00

11 TO 50 15.00

51 TO 100 9.00

101 TO 250 5.40

In this there is an area where it is cheaper to buy for example 12 instead of 10 and I am trying to work out a formula to deal with this funny step change down as people buy more.

I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.

I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.

If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.

For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View RelatedThe analysis basically has 2 data components to it:

The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.

The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.

What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:

1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)

2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)

3) Pull the price for this "closest quantity"

I have uploaded a worksheet showing the structure of that data.

[url]

Is there some VB code I need to do this, or can it just be a few simple formulas?

All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)

Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines

Sheet2 (has current material codes plus current pricing), has about 36000 lines

I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,

I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?

I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.

I would also like to reference the source on the next column.

I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:

2008 2009

$23.95 $24.15

Using excel 2007

I would like to integrate a button on my spreadsheet that looks and works much like a round volume button on a stero that you can turn in order increase or decrease volume.

However, I would like the button to control the values of a cell. If the volume is turned down, it should display a 1. If the volume is turned up, it should display a 16545. In between you should be able to - more or less - tune in on any integer in between.

a formula that would interpolate the volume in the below worksheet....

View 9 Replies View Relatediam trying to use vba to work out the volume of a cylinder.

If the cylinder's dimenstion are:

Diameter = 200mm

Height = 800mm

the Volume should = 0.00017 cubic meters.

Iam getting 0.502654824574367 as the answer using this code.

Private Sub CommandButton1_Click()

'http://www.readymix.com.au/toolbox/calculators/calcConcreteRoundColumn.asp#

Dim RadiusSqared As Double

RadiusSqared = (txtHoleDiameter / 2) * 2

txtCubicMeters.Value = 3.14159265358979 * RadiusSqared * txtHoleDepth.Value

End Sub

Is there any way to add percents to a stacked bar graph that also displays volume? Right now in order to show percents in a stacked bar graph I have to manually enter each percent for each segment of the stacked bar.

For example, I am showing male and female volume in each bar (50 males, 50 females) and the Y axis scale will be stacked up to 100 for the two. In the actual bar I want to show 50% for the value in each stacked bar.

Any way to show both Volume and Share in the same stacked bar? I just want the Y-axis to read the "count / volume" and the actual bar to display the percent. But have just resorted to manually updating everything and it really takes way way too much time.

I have data in "column F" that I want to highlight. The data is Listed verticaly from Row 1 to 424 in Column F; I want the spread sheet to hightlight the number when it exceeds the previous days 30,60, and 90 day average volume. How I can make this occur?

View 2 Replies View RelatedI have this formula below which works for finding the Volume Total in range C7:E7...it displays the volume for that date in G7

Code:

=SUMIF(INDIRECT("'"&E$6&"'!B7:B10000"),$G$7,OFFSET(INDIRECT("'"&E$6&"'!B7:B10000")

,,MATCH($E10,INDIRECT("'"&E$6&"'!$C$7:$E$7"),0),,))

but when I use the formula for finding the PLanned Stops - Time in the range G7:H7 it gives the Volume Total from Column D...I want the Planned Stops -Time from H7.....

Code:

=SUMIF(INDIRECT("'"&E6&"'!B7:B10000"),G7,OFFSET(INDIRECT("'"&E6&"'!B7:B10000")

,,MATCH(E13,INDIRECT("'"&E6&"'!G7:H7"),0),,))

I have a spreadsheet with every day of the month where I input quantities of purchased product. We divide those quantities according to the number of days of pricing period.

So if we purchase volume of 100 on the 1st of the month on a 10 days pricing, I would like to have in my spreasheet a volume of 10 for each 10 first days. Easy so far, where I block is when I start having several deals, at different days and with different pricing period.

See attached file for example.

How can I increment my column? Would it be Macro or can I just stick to formulas?

Need a way of getting one of 4 values in sheet 2 to perform a calculation with. The aim is to show the total volume of a given item. Because there are four data sources for the actual volume (with varying degrees of accuracy) I've incorporated all of them in one sheet but want to use the values in precedence i.e. the first figure is where I've had someone physically tell me how many fit to a pallet (cubic metre) and know that to be accurate. If the value there is 0 I want to use the next figure as certain other items were measured physically. Column three and four are from some legacy systems and therefore should only be considered if the figures are 0 in the first two columns. Rather than ramble on any more I've attached a sample sheet

View 4 Replies View RelatedI need to get the sales volume from another worksheet but need to meet 2 criterias in both col A and B. How can I do it? Can I use Vlookup for this?

I'm attaching a file here. The cell highlighter in yellow is where I need the sales volume. First I have to find the region, then the brand of battery to get the sales volume.

I have two different data sets that I have plotted using XY(Scatter) w/ smooth lines. Is there a macro or sub that will calculate the volume or area between where the two data sets cross?

View 3 Replies View RelatedI am currently doing some work which is taking ages and i wonder if there might be a quicker way of completing the work.

Each Row contains a User ID alone with the volume of business submitted under that user ID via a specific payment route, the problem i have is that i may have 10 of the same user ID's but each with a different business volume and payment route attached, I have already sub totaled by User ID so tat can see total business volume for each user however, I want to delete all the duplicate user IDs leaving only the row with the max volume of business attached.

I have approx 40,000 user ID's so i need the solution look for sets of User IDs and leave only the one with the biggest volume of business attached to it.

I have a drop down list with 'Weekly', '4 Weekly' or 'Monthly' as the selectable values.

I want the cell below to calculate a number in an adjacent cell with either 52 for weekly, 13 for four weekly or 12 for monthly.

I.e 100 and weekly would display = 5200 ( 100 x 52 )

100 and monthly would be 1200 = ( 100x12 )

I have been trying to multiply cells(6,3) of sheet 'Input2' with the 23rd row of next sheet 'Input' from 4th column in that row to last used column. Then I want this looping to go from 23rd row to the last used row in that sheet. But I am not successful. The code that I tried till now is:

Sub test()

Sheets("Input").Activate

x = Range("D23").End(xlToRight).Select

For j = 4 To x

m = Sheets("Input2").Cells(6, 3) * Sheets("Input").Cells(23, j)

Sheets("worksheet").Activate

Sheets("worksheet").Cells(j, 17) = m

Next j

End Sub

to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.

View 9 Replies View RelatedI have a table (B2 to G11) that include an earthwork output. This table is variable in size (only vertical -- i.e., the same columns are used but more rows may be filled with data). All the formulas are included in the workboook.

Basically what I need is the volume between two non-consecutive stations, for example: "Calculate the volume between stations 305.00 and 402.55"

The solution to that problem is shown in the Excel Sheet step by step, if you need any clarification please let me know. I know that the process I show can be done automatically maybe using VBA, macros, or combination of functions in Excel.

I have 30 and above sheets in a work book and like that I am having 5 such

books. The sheets are named as 201, 202, 203 ....etc as per the contents in

that particular sheets. (201, 202 .....are the P.O nos.). all the work

sheets are of having similar format of datas.

Now what I need is if I want to look the details of one single sheet (say

324) I have go all the sheets one by one and it is hard to find out.

If any body give me a solution so that if I type a particular no. (forms

part of the name of the sheet) that sheet should appear for me.

I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.

I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):

= SUMPRODUCT(price, quantity)/SUM(quantity)...

I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.

View 1 Replies View RelatedBasically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e

Sheet 1

100

Sheet 2

2,5,9,15,20....

I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....

I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.

I have these two formulas I am trying to multiply

=ROUNDDOWN(AK170/Sheet1!B3,0) Returns a value of 2

=ROUNDDOWN(AL170/Sheet1!C3,0) Returns a value of 7

I need to multiply these 2 formulas together to return a value of 14

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