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?

View 2 Replies


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

View 9 Replies View Related

Calculate The Weighted Average Of The Win Rate Based On Volume Of Calls

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

Highlight Cell When Row Data Changes From Previous Row

Jul 7, 2013

I have a spreadsheet with over 6k rows, each with over 30 columns.

My focus is in 2 columns. ColumnA is formatted as General, ColumnB as Number.




[Code] ........

I need VBA code that will Fill the cell in ColumnB if it changes within the same ColumnA data.

So the output would end up like this. (Although the cell would be filled, not the text turned red)



[Code] .........

So it's pretty basic. If we simply remember the first ColumnA and ColumnB data, go to the next row, if ColumnA is the same, then ColumnB should be the same. If it's not, then fill with red, go to the next row, repeat. if ColumnA changes, remember the new pair, go to the next row and compare again.

I've thought about this from a scripting perspective, and imagine that something like this would do the job:

sub FindBadTermID ()
Dim row As Integer
Dim dataA as string, dataB as string
Set row = 2


[Code] .......

View 9 Replies View Related

Highlight Date If Previous Month

Mar 13, 2008

Need to be able to highligh data within a range where the date in a column is for the previous month/s- therefore need to be able to also include in macro entering the current month - don't want to have to edit macro each month

View 2 Replies View Related

Conditional Format Rows To Highlight Every Other Row With A Value Different Than Previous

Feb 11, 2014

do a conditional format rule that will highlight every other row of my excel spreadsheet when the value in a specific Column (say Column A) differs than the previous.

I've attached a sample worksheet with what I want it to look like after the conditional format rule is applied (every other row highlighted in light blue). The rule needs to apply to all rows in the worksheet beginning with ROW 2 (I don't need the rule to apply to the column header which is in ROW 1).

View 4 Replies View Related

Highlight Column And Row Of Active Cell But Retaining Previous Formats

Feb 28, 2014

I have found this code by searching witch is perfect

If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target 'With the cell that was selected,
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True

The point is that i want to retain the previous formatting of cells when i select another cell. And also i would like to create a switch to turn the Worksheet_SelectionChange event on and off. The tricky part is here i guess, becouse i would like when turned off to retain the previous formatting also.

View 2 Replies View Related

Calculate The Average Of The Previous 12 Months

Oct 14, 2009

I'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:


I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.

View 4 Replies View Related

AVERAGE Of The Previous Meaningful Cells

Feb 26, 2006

B10 = AVERAGE(A1:A10),
B11 = AVERAGE(A2:A11), etc.

In case there are empty cells in the A-column, the result is not correct
anymore because AVERAGE does not take en empty cell into account.
For instance if A1:A10 are 1, A11:A20 are empty and A21:A30 are 2, the above
formula in B21 would give AVERAGE(A12:A21), or 2.

What I want is the average of the previous 10 meaningful cells so - in this
case - the average of A2:A10 and A21, or (9*1+2)/10 = 1.1

Question : Is there a possibility to calculate the average of the previous
10 meaningful cells (0 is meaningful, blank is not)

View 12 Replies View Related

Auto-highlight A 'below Limit' Number In An Average

Aug 22, 2006

I'm trying to create a gradesheet in which any score that falls into the
failing range will automatically highlight in red...

View 2 Replies View Related

Excel 2010 :: Highlight Number When It Exceeds Past 30 Days Average

Jan 20, 2012

Excel 2010 Higher than past average formula?

Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..

View 3 Replies View Related

Find Last Previous Non Blank Value And Summarize Previous 6 Months?

Dec 10, 2012

see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:

Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012

View 7 Replies View Related

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.

View 9 Replies View Related

Interpolate The Volume In Worksheet

Jan 23, 2009

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

View 9 Replies View Related

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

View 11 Replies View Related

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.


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

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.

View 3 Replies View Related

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


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


View 4 Replies View Related

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?

View 3 Replies View Related

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

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?

View 4 Replies View Related

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.

View 2 Replies View Related

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.

View 14 Replies View Related

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?

View 3 Replies View Related

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.

View 9 Replies View Related

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.

View 3 Replies View Related

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.

View 12 Replies View Related

How To Compare The Sales Volume Of Identical Products In Two Separate Worksheets

Sep 3, 2013

This is how my face looks like at the moment.


Worksheet company A: Filled with products

Worksheet company B: Filled with products

Is there a way to copy/export the products from A&B into a new worksheet and compare the sales of these products to eachother?

I want to compare the salesvolumes of the products (of A&B).

Goal of this comparement: See if products need to be moved from A --> B or B --> A.

Hopefuly this is a simple question for some of you, and hopefuly there is a person who will take the time to answer my call...

View 2 Replies View Related

Landscaping Company / Matching Units / Volume And Labor Hours To Hourly Labor Rate

Jun 26, 2014

For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. I also have a sample file.

I have, on Sheet1, 8 columns I'm working with:

R3:R132 is the currently hourly rate

L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.

AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.

AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.

On Sheet2, I have 4 columns I'm working with:

A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.

They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.

My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:

1) Attempt to reduce labor hours to get to suggested hourly rate - This is to calculate new labor hours on Sheet1 in cells AF3:AF132.

View 1 Replies View Related

Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies View Related

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