# variance

1. ### Calculating Variances (Value and Percent) in Pivot (not using power pivot) - is it possible?

I have financial data that I need to show variances against a number of dimensions. I need this in pivot so that I can use filters to create automatic views for different audiences (e.g. Divisions and Heads of Areas). In the pivot there are roll ups, so example income as a level with sub level...
2. ### Concatenate: Italicize (or bold) part of output

I have a concatenate formula to output into a cell something like "100% / 75% / (25%)" (code is below). I'd like to either bold or italicize that 25%...something to show that the (25%) is a variance and not a raw value. It needs to be something other that "red" font since sometimes the...
3. ### Comparing Weeks with today and selecting the right column

Hi All, I am trying to create a column that is basically, giving the variance of this week, to the previous week. I want to make this as automated as possible. An example is below: So in the table there are 5 columns with 5 dates, and I need in the 6th column to have O2-N2, and I need this to...
4. ### Calculated Field Pivot Table

Hello All, I am trying to create a calculated field in a pivot table so that i can find the variance between the current years beginning account balance and the prior years ending account balance. <tbody> A B C D E F G H I 1999 1999 2000 1999-2000 2000 2001 2000-2001 001 Beginning...
5. ### if statement for +/- a percentage

Hello, I'm trying to display a tick or cross in F if a variance (D-C) is less than or greater than C but i'm having a total mind blank. Should be easy but its not! I've got the wingdings side easily just the formula is stuffing me around So Num1 ...Num2 ...Variance.....Mark...
6. ### Goal Seek a Range Where the Total Variance of each Row and the Total Variance of each Column must be = 0

Hi, I will explain.See the table below <tbody> a b c TOTAL TARGET VARIANCE R1 1 1 2 3 -1 R2 1 1 2 2 0 R3 1 1 2 1 1 TOTAL 3 1 2 6 6 0 TARGET 2 2 2 6 VARIANCE 1 -1 0 </tbody> The total target of the rows and columns will always equal each...
7. ### Need SUMIF/SUMPRODUCT formula with criteria in non-consecutive columns

Excel masters, I need your help! I'm doing a monthly variance analysis and need a year-to-date formula that will calculate only columns that have monthly actuals populated in them. For example, I have 3 columns for each month (actuals/budget/variance) and a total year-to-date at the end...
8. ### Optimized scheduling to demand based on constraints

The title kind of states my goal. I am looking for some help to get started to solve the problem of scheduling a group of people based on demand and their individual constraints, using solver and VBA (or maybe a combo of the two?) Here is a picture to describe what I am looking for help to...
9. ### Subtotalling Similar Rows (VBA?)

I've made a spreadsheet which compares values from two sheets (the values in Column I and P in the image link below) and then calculates the differences in Row Q, all simple stuff so far. The problem I've got is some common values that I use to draw the info (Column F - highlighted) have...
10. ### Trying to sum YTD data using couple different criteria

Looking to sum Actual, Budget, Variance YTD data in last three columns in the correct column based on the number in current period, tried sumifs, sum/index, and sumproduct didn’t work. <tbody> Current Year 2019 Current Period 2 Actual Budget Variance...
11. ### Popup warning when swithcing sheets in workbook

Hi all, I am generally pretty good with Excel but I havenever worked with visual basic or writing macros. In my current role I ambuilding a template for project managers to use to forecast. One of the itemsis to check the number of people forecasted against the prior month peopleworked. I did a...
12. ### Indirect Formula to A Named Range Different Workbook

Good morning All, I am struggling a bit with the indirect formula where I am trying to reference a named range in a different work sheet. The formula is retrning #ref and the formula is: INDEX(INDIRECT("Statements & Variance Model.xlsm!"&"Source_Data"),ROW()-5,COLUMN()) The named range is...
13. ### Variance calculation between values from separate files

Hi all, I am beginner in Excel VBA, just starting to learn it and I would really need some help with the below problem. I am struggling to make it work with no success so far… I am starting with 3 separate files (one having an amount of today, the second an amount of yesterday and the third is a...
14. ### Is it Possible for Excel to select Column Chart Data Source Dynamically??

Hey Guys, Currently using windows 10 and Excel 2010. Is there a way for excel to select the data source for Column Charts Dynamically? Example : <colgroup><col><col><col><col><col></colgroup><tbody> Australia Vietnam Indonesia Mexico Quarter 1 10 5 7 2 Variance 1 8 3 5 0 Quarter 2 11...
15. ### Calculating Income Surplus/(Deficit) w/ Negative Expense

I am trying to find a formula that will handle calculating "Income Surplus/(Deficit)" regardless of Total Operating Expense calculating a positive or negative number. For example: Projected YE Income is 5,260,162 Projected YE Expense is 6,328,910 Hard Income Surplus/(Deficit) is -1,068,748...
16. ### Variance Percentage in Pivot Table

I have a table that looks something like <tbody> Person Month Target Actual Variance % A Jan 1075 1500 28% B Jan 4109 4950 17% A Feb 1084 4000 73% B Feb 1000 2250 56% </tbody> The pivot table has a slicer that filters by person which works great when only looking at one at a time...
17. ### using SUMPRODUCT with DATE

I am building inventory report to show how inventory is doing When I do counts for each day I enter manual data into the excel table i have build like in first column I have Date of cycle count, next part#, Stock location, physical count, variance value \$ I tally all the numbers on a separate...
18. ### VBA to Create Rows based on date differences

Hi All, Hopefully a very simple query - which maybe covered within the forum - yet can't find or pinpoint what the search criteria would be to locate it. Simple four column spreadsheet all unique, the last three columns are start date, end date, variance (the other one is name) I'd like a...
19. ### Formula/UDF for Variance for Folded Normal distribution

I am looking for a formula/UDF for computing the Variance of Folded-Normal distribution. The formula for the same can be referenced at https://en.wikipedia.org/wiki/Folded_normal_distribution
20. ### Difference between last column in a table and another column

Hi guys, Could you please help with any ideas with the below problem that I have? I would like to calculate in the column where it says "Variance" the difference between last column where it finds values and the column before it (April -March). I have added in cell G1 a dropdown list (bold)...