1. Johnny Thunder

    VBA - Code to search in Header Row for String and Return Values in Column Below - Excel 2010

    Hi Everyone. I am in need of a code to populate a variance checklist for accounting purposes. I have two sheets within one workbook, sheet "Tie out" that contains about 50 columns of data, but only 12 Variance columns. I need a code that can look at Row 1 and search for cells that contain the...
  2. D

    Calculating % Variance between actual and budget

    Hi I am using Excel 2013 and looking at setting up a pivot table and chart to show: Freight Cost Freight Budget Variance % I can't seem to get the variance column to show the percentage change between actual and budget correctly Can anyone help me with a formula? Thanks
  3. T

    Using MMULT MMULT TRANSPOSE and getting #VALUE!

    Hello Forum. This is my first time posting so forgive me if I make any errors. First note, I have tried CTRL+SHFT+Enter and this did not solve my issue. Second not, I am using Excel Professional Plus 2010 A little back ground. I have built a spread sheet to use for building ETF and Mutual...
  4. M

    Find and delete rows that contain date's that change.

    Trying to take a report that is copied from another program into excel and remove the unneeded data and format it into a usable format. Code Currently using to remove Dates: If Cell Like "Tuesday*" Or Cell Like "Monday*" Like Cell = "Wednesday*" Then Cell.EntireRow.Delete End If It...
  5. J

    Address Function to increase reference cell by 1

    Hi, I'm working on a project and I need some help with the address function. A few notes for reference. Sheet I'm working in is called 'AvB' The sheet I'm drawing data from is in the same workbook and is called 1) 'BS' Background: The sheet called BS stands for balance sheet. On that sheet...
  6. H

    VBA code to display where there is a value in Col D next to "Variance"

    I have written code to check if there is a value not equal to zero in Col D in the same row as where text "variance" appears in Col A I want the code to check if there is a variance (Value not equal to zero) in Col D on all sheets, except the last sheet The code runs, but does not display a...
  7. R

    Remove aging list if variance is zero

    I have been put in charge of an inventory of AR items. Please see below STATUS Acct. Number MS4 Bat # Date In (Column E) AGING (Column F) Date Out Amount In Amount Out Variance Refunded 02/03/17 91+...
  8. S

    HELP! I've got no VB exp and need to use it. Average values across multiple workbooks.

    Im trying to find the average values of cells across multiple workbooks. Problem is, the values aren't continuous and the amount of values requires may change in each work book. Im trying to average a employee's worked variance. There work can change through the day so it would need to search...
  9. D

    Conditional Formatting for Variance - Forecasted and Actual Dates

    I have a set of forecasted and actual dates for different projects. I want to be able to apply conditional formatting to the actual dates that fall before the forecasted date (in green) and after the forecasted date (in red) See the file here...
  10. D

    Estimate variance conditionally in Excel?

    Hi All I have two columns. Column 1: categories 0-4 Column 2: various lengths of time n=504 Could anyone tell me, how I can calculate the variance in time according to what ever category is picked, in Excel? Thank you very much Declan :-) PS This is my first post
  11. D

    Automatic array determination upon criterea

    Hi, I have been looking ages for this solution in order to complete my thesis. I have a dataset with daily stock prices of many firms. The firms however are listed under each other, and therefore I need to determine the array upon a critera. For my first BETA calculation, i have used the...
  12. M

    Minimize Variance and Optimize

    I am currently working on a project to minimize variance and need some help to see if Excel can optimize this. The way I'm defining variance is (MAX-MIN)/AVG. I have a set of 60 numbers being input into the spread sheet. These 60 numbers need to be separated into 10 groups of 6 such that the...
  13. M

    Need List of top 25 over and top 25 under

    I am trying to list the top 25 combinations 'over' and 'under' based on the combination in column O by either using the variance in column R or the amount 'over' and 'under' in columns S and T respectively having the top 25 'over' populate in column U and the top 25 under populate in column V...
  14. C

    Forecast returns Div/0 but my variance is not zero?

    I have the following dataset: <TBODY> 2008 2009 2010 2011 2012 2013 2014 A 49 44 38 33 42 32 B 245 269 253 323 374 335 C 122 157 120 175 190 174 D 202 227 174 271 282 246 E 219 224 191 214 250 135 F 87 99 95 94 98 103 G 92...
  15. D

    Pivot calculation - variance between count of 2 fields

    I'm needing to create a pivot table that dynamically generates a variance between two data fields containing attribute data. I've put a mockup on Dropbox to show the source data (after denormalizing) and the output table I'm trying to produce. I know that I can perform a subtraction either using...
  16. D

    How can add calculated Variance to Pivot table when Budget & Cost are from different tables in the data model

    Please bear with me, I'm a newbie! I'm using PowerPivot in Excel 2010 and am trying to create a calculated Variance field in a pivot table rather than add a column to my source tables as my Budgets and Costs come from 2 separate sources. I have 4 tables in my PowerPivot data model: Budget...
  17. M

    Cumulative Average Formula

    <COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 54pt...
  18. J

    PivotTable Percentage A-B

    I have a very large set of data (7000+ rows) that I am using a PivotTable to summarize. Simplifed, PivotTable looks like following. "Revenue" and "Expenses" were in the raw 7000-row data, and I created a PivotTable "Calculated Field" for both "Margin$" and ""Margin%". Issue I am having is...
  19. J

    Calculate Item (Difference of calculated fields)

    Hey All, In Excel 2007, I have calculated fields which take calculates a percentage. I added a variance column but the calculated field in the variance column calculates the the percentage of the variance as opposed to the change. Any advice or any recommendations? Thanks, JV SDG
  20. S

    Creating a budget variance column against current month actual and budgeted projections, same year

    I am trying to come up with a formula to show me in real time where my actuals are against my budget. The problem I am having is, I will be entering actuals as they come in, so I do not have data sets to simply run the percentage off of. I have my yearly budget by month, and a column for the...

Some videos you may like

This Week's Hot Topics