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
View 4 Replies
ADVERTISEMENT
Nov 23, 2009
I have 3 sets of data for two different groups:
Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)
I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?
View 6 Replies
View Related
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()
'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
View 11 Replies
View Related
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?
View 3 Replies
View Related
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.
View 3 Replies
View Related
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?
View 6 Replies
View Related
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.
View 2 Replies
View Related
May 5, 2013
I have a number which consist of 12 digits such as 765432123345. The first two digits denotes the year. I need to create a formula in the next column to calculate the difference in years by comparing to the current year of 2013. In this scenario, the difference will be 37. Is there a way to do this?
View 5 Replies
View Related
Jan 17, 2009
We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase. My questions have two parts:
1. Is this something array formula can handle in one formula?
2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps. For example,
Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30......................
View 5 Replies
View Related
Jun 8, 2006
I am working on my companies tax reports for the first time. The wages for unemployement and FUTA do not calculate IF the total wages are over a certain amount.
Im trying to get the formula to see if the total wages are over $7000 then dont calculate. Ive tried several different things from the links, but still can't make it work.
total wages are $7800, so i only need to calculate it on $7000 and then not calculate it again for the next quarter.
View 3 Replies
View Related
Jul 29, 2006
I have events in column 1,in my sheet there are about 800 events.
In col 2. each contestant has a label,blue(b),green(g) or red(r)
In column 3 I want to print the % of blue + green of the total for each event,
e.g first event in attached sheet would show 77.7% down to row 10 in col 3 etc.
View 9 Replies
View Related
Apr 20, 2007
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
View 6 Replies
View Related
May 20, 2007
I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.
Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?
The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).
View 9 Replies
View Related
Jul 26, 2013
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided
Total KM run
Basic Amount
Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
View 8 Replies
View Related
Aug 30, 2006
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.
View 3 Replies
View Related
Sep 27, 2012
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
View 4 Replies
View Related
Jun 15, 2014
I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.
DOWNLOAD WORKBOOK: [URL]
I have two problems:
The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.
I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?
Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.
View 1 Replies
View Related
Jan 8, 2010
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies
View Related
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.
View 9 Replies
View Related
Jan 23, 2009
a formula that would interpolate the volume in the below worksheet....
View 9 Replies
View Related
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.
View 3 Replies
View Related
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?
View 2 Replies
View Related
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),,))
View 4 Replies
View Related
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?
View 3 Replies
View Related
May 6, 2008
What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?
View 4 Replies
View Related
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.
View 14 Replies
View Related
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.
View 9 Replies
View Related
Nov 25, 2013
In the first tab of my workbook I have a data table. In K2 down of that tab is a list of names, in D2 is their form, in I2 is the Type (there are 3 Types which start with (R), (P) or (S), in J2 is the points score associated with the Type and in H2 is the date they received the points.
For each name in the table I need it to sum the running points totals for (R), (P) & (S) Types and when they reach the minimum points thresholds below I want it to record the earliest DATE this threshold was achieved.
School Award: (R) = 20, (P) = 40, (S) = 20
Bronze: (R) = 40, (P)=80, (S)= 40
Silver: (R)= 60, (P)=120, (S)= 60
Gold: (R)= 80, (P)=160, (S) = 80
The output should look like:
Name
Form
School Award
Bronze
Silver
Gold
Anders Peter
7BN
20/9/13
15/11/13
Ball Jim
7BN
20/11/13
Carter Mark
7BN
19/9/13
8/11/13
24/11/13
View 6 Replies
View Related
Dec 29, 2007
i want to do is i have an excel spreadsheet...and i have two columns in there that are called Representative and one thats called Result....The Result column has Closed, Rescheduled, and Not Interested....so what i want to do, im looking for a formula that will calculate how many CLOSED are from a certain Representative....so if i have 5 Closes for John...im looking for a formula to calculate those 5 for John...but i dont know what formula nor how to use two diff. ranges with two diff. criterias....
View 7 Replies
View Related
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.
View 12 Replies
View Related
