# Value By Summing Volume & Multiplying With Price

May 6, 2008

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

## Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

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

## Adjusting Price Based On Volume?

Sep 17, 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?

## Price Breaks Based On Volume And Threshold

May 22, 2013

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.

## Differencing Sets Of Numbers, Multiplying, And Summing

Sep 1, 2008

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.

## Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

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.

## Lookups For An Item's Price From A Price List

Jan 4, 2010

The 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?

## Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)

Jul 24, 2006

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,

## Looking For The Closest Price To A Reference Price

Feb 12, 2010

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.

## If Statement: If The 2009 Price Is .50 Or Less Away From The 2008 Price, Bring Back "Check"

Mar 17, 2009

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

## VBA Volume Turn Button

Dec 11, 2008

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.

## Interpolate The Volume In Worksheet

Jan 23, 2009

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

## Calculate Cylinder Volume

Jan 13, 2007

iam 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()
RadiusSqared = (txtHoleDiameter / 2) * 2
txtCubicMeters.Value = 3.14159265358979 * RadiusSqared * txtHoleDepth.Value
End Sub

## Adding Percents To Volume Graphs?

Feb 27, 2008

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.

## Highlight Above Previous Average Volume?

Jan 21, 2012

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?

## Finding Volume Total In Range

Sep 15, 2013

I 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),,))

## Sum Volumes According To Date & Volume Split

Dec 7, 2007

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?

## Calculate Volume Based On Criteria

Dec 22, 2007

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

## Using Vlookup To Get A Sales Volume But Need To Meet 2 Criterias

Nov 6, 2008

I 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.

## Calculate Volume From Two Datasets On Scatter Plot

May 8, 2012

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?

## Delete Multiple Rows But Not With Max Volume Of Business

May 8, 2007

I 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.

## List And Multiplying By Value

Oct 2, 2013

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 )

## Multiplying A Cell With A Row

Jun 8, 2009

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

## Making Average Buy Price And Average Sell Price

Aug 13, 2008

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.

## Calculate The Volume Between Stations (Civil Engineering Earthwork)

Dec 18, 2008

I 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.

## Selecting A Single Sheet From A Volume Of Sheets In A Workbook

Jun 30, 2006

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.

## Multi-Condition Volume Weighted Average Calculation

Sep 13, 2006

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)...

## Multiplying And Subtracting Within Cells

Feb 5, 2014

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.

## Multiplying Values In Excel

Apr 7, 2014

Basically 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.

## Multiplying Formulas Together To Return A Value Of 14

Feb 9, 2010

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