Corroborate Calculated Field with airSlate SignNow

Decline calculated field

hello and welcome back to chips and time-savers I'm Danny rocks in today's lesson I'm going to respond to a viewer request viewer wrote the sedan II I'm an experienced user of Excel 2007 I use pivot tables all the time and for the most part I'm satisfied with the results I get from calculated fields however five to ten percent of the time some of the results I have to question can you help me yes I can all right let's take a look over here we've begun to create a pivot table from the underlying data over here in our underlying data we have four fields date product units and price per unit now we realize that when IT gave us the file or when we made a web query we forgot to add in a field for total revenue so we're thinking well I've heard about calculated fields this might be a good opportunity to test this out so what we want to do is be in one of the fields that contain values when we create a calculated field it can only reside in the values area of our pivot table so I have one cell selected in the sum of units in Excel 2007 Excel 2010 go up to pivot table tools options and you find calculated fields as a menu underneath formulas so let's give this a name what we want to do is call this total sales and as we look at this we understand that all formulas begin with an equal sign as we plot it out it's gonna be taking units sold plus the price per unit that will give us our total sales so delete the zero placeholder and down here let's select our first field units let's use the insert field the operator that we're going to use is multiplication and for the price per unit this time let's just double click it so here's our formula equals units multiplied by price per unit and let's click OK and see what we come up with even without the house and separators this doesn't look good so let's go in and format the field remember that at a pivot table we format a field not individual cells so in this case let's just look at the number format let's put currency in there with zero decimal places and click OK and you know just as I suspected 2.2 billion we're only a 2.4 million dollar company so something is wrong well we're bound to determine to make this right so we say all right let's take a look at this maybe if I put the date field in there that will give me a better representation now here's what you have to remember about calculated fields and first off in Excel over here in the underlying data set notice that we have multiple records for January first in 2010 but in the pivot table when we look at the date field it summarizes all of the transactions into one row into one record so over here the sum of the units for January 1st is a combination or a sum of these two records the combination for the price and let's add this into the field so the price per unit over here $13 is a sum of that field so that's what we want to need to understand about calculated fields they operate on the sum of the field in your data set so this is obviously an instance where we need to make a change now during this lesson I'm going to give you three strategies for fixing this problem three workarounds for calculated field trouble spots in this case we realize upon examination that what we need to do is we need to add a field to the underlying data set so let's come back here and first off let's remove this price per unit we don't need that but we need to get rid of that calculated field total sales here's what we do go up to pivot table tools options formulas calculated field the gotcha step is over here in the name there's a drop-down most people forget this and select the calculated field that we just created click delete and then click OK so off that goes and for right now let's change this from day to product it just makes it a little bit easier now we realize that in the underlying data set what we need to do is add in an additional field let's this time call it sales and now the formula is going to be equals units multiplied by price per unit and we'll double click the autofill button to copy that formula all the way down let's leave these as formulas it really doesn't matter however what does matter is that for the pivot table the pivot table does not automatically update what we need to do is come in here on pivot table tools options and in the data group we need to change the data source so for our original data for the pivot table we had 4 columns selected remember we just added a fifth column so what we need to do is come over here and add in column E so it's gonna go from column a through column E click OK and now when we look at our pivot table field list sales have been added in now let's add the sum of sales in to our pivot table and of course we want to go through and I'd like to change the name and I'd like to format the field to use currency and so right mouse click value field settings when we change the name I wanted to read as sales however there's going to be a problem if I stop here and say ok I know I'm gonna get an error so click OK told you I'm gonna get an error what we can do when we want to have the name be the same name as the field here's what you do add a space at the end so sales space Excel considers as a separate name as a different name from the field sales all right now let's apply currency as the number format currency with zero decimal places click OK click OK now let's go through and add in a calculated field that we know will work in this case what I want to do is I take this into the manager and the manager says Danny what I want to see is I want to see the average per product sold the average unit sale per product that we sell so again make sure that you have a single cell selected in one of the fields that are in the values area of your pivot table come back to pivot table tools options formulas calculated field in the dialog box let's call this AVG average unit sale and we know that what we want to do for the formula after we've removed the zero placeholder is take the sales field and divide that by the units that we sold click OK well sorry what did I do I forgot to add it I didn't double-click it there you go so either double-click it or click insert field now we're all set so here is our average unit sale now again I want to change the name and I want to format this to currency so right mouse click one cell value field settings I want to change this to average units sale and again I add a space so that Excel will treat that as a separate name that's acceptable change the number format in this case the currency I'm gonna leave two decimal places click OK click OK now I like to go through and I like to verify my calculated fields and actually this brings us into another opportunity to change or correct them use a workaround for calculated fields when we have trouble spots in this case what I'm going to do is I'm going to write a formula outside the pivot table but I want to draw your attention to the default setting in a pivot table which includes the get pivot data option all right so let's come back here what I'm going to do is I'm actually going to create a field outside of the pivot table so outside of the pivot table I'm gonna call a field average and now if I come in back into the pivot table and again I can tell when I'm in the pivot table I have the pivot table options and pivot table design over in pivot table options if you look at the drop down for options you'll see that there's a generate get pivot data and by default it's enabled now what that means is that if you go to write a formula and if you use your mouse to go over here and say I want the sales well look over here what's this a crazy formula again well let's go along with it divide it by and again use your directional arrow or the mouse to come over this is a crazy formula but oh well let's see click okay yeah that looks fine now remember we're outside of the pivot table so I can use formattings for the cell and I'll leave two decimal places click OK and click down I can't pot it this is impossible there's no way that all of the sales are exactly the same for the average unit sale five dollars and seventy two cents so if you leave this default setting get pivot data in place you're going to get a copy of the formula and exact copy now here's what you can do to change it unless you know to go in and change the setting forget pivot data in this case equal don't use your directional arrow don't use the mouse instead we want to type in the exact cell so in this case it's going to be I seven so we want to say equals I seven divided by H seven so now we have the formula that we want and when we copy it down now we can see that the formula that we wrote adjacent is a great way to verify our calculated field but it's also another great way - when you have a problem with the calculation that a pivot tables calculated field returns this is one way to correct it it's a workaround now let me again go back and show you how to remove that get pivot data nuisance over here so inside the pivot table pivot table tools options come over here and on the options drop-down toggle off the get pivot table data so now you could use your mouse now you can use the directional arrow alright let's come over here and look at a different data set over here we have date we have product units price per unit well that's all familiar and we added in total so the other additional fields that we have are unit cost and cost of goods sold clgs now notice over here that in the pivot table I've already created one calculated field gross profit what was that formula so let's come over here pivot table tools options formulas and calculated field drop-down let's take a look at the formula so in this case it was the total field so we took the total field and we subtracted the cost of goods sold now notice that we do not have to have all of the fields in place for this all right now let me show you how to create a different calculated field what we want to put in here is we want to put in here the gross profit percentage so an additional calculated field pivot table tools options formula calculated field we'll call this GP percent and the formula for gross profit percentage is going to be the gross profit which remember we already used as a calculated field we created this as a calculated field and we want to divide that by our total sales divided by our revenue and again click insert field or double click it click OK here's our second calculated field and again let's use our value field settings we'll change the name we wanted to read GP percent but in order to accept that put a space bar after that in our number format let's make it a percentage with one decimal place and click OK click OK again now the third strategy that I'm going to recommend for fixing problems with calculated fields is to get your pivot table 90% of where you want it to go to and then use a copy and paste values from so over here one thing that we might want to add into here is we might want to take advantage of the pivot tables ability to group daily dates into months and quarters so with one cell selected let's right mouse click and what we want to do is group this field according to months and according to quarters now it's one year it's the year 2010 so click OK and now we actually have created two new calculate or another calculated field called quarters the original field was called a date but it really is the month so over here what I could do in pivot table tools options in the active field come over here and change this to month and now I've taken advantage of the power of a pivot table and now what I'm gonna do is I'm gonna make a copy of the values of the pivot table so I can further refine it so what I like to do is come up here to pivot table tools options select the entire pivot table make a copy of it and then come somewhere else either in the same worksheet or on another workbook or another worksheet and we don't want to paste what we want to do is pay special the values click OK and there we have a copy now that copy is going to function as the result of our pivot table so we got our pivot table 90% of where we wanted to go to when we want to perform additional calculations specialized calculations work with a copy of it or we have the option of writing a formula outside of the pivot table remember to remove that get pivot table data or type in the cell references before you copy it down but there also could be a problem with the way we're trying to create a calculated field what we really need is to add an additional field into the underlying data set so there you've learned some valuable tips about how to work with pivot tables I also have extended length videos on pivot tables I've created them for Excel 2003 2007 and 2010 they run about 90 minutes in length and you can download the video for only $9.95 and I'll look for you in the next lesson