Can you use a function in an Excel calculated field? Well, Bryony from the U.K. says yes you can, and sure enough...it works. Episode 878 shows an example of using a function to create a new field in a pivot cache. Fairly cool.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, trust me, I am Bill Jelen.
You can't see me; we're battling the Sun here.
It's Akron, Ohio; it's not a sunny place but, for some reason, it's sunny.
And while the Sun is usually straight over there, and we can block it out fairly well with the wall, it's now gotten down to the south and so it's coming into the room and I'm all washed out.
But that's okay, still time for another cool Excel tip.
Talking about Pivot Tables today, and calculated fields in a Pivot Table.
Got of note from Brian, E. Brian, E. is, of course, in the UK.
If you're out at the MrExcel message board, you've probably seen Brian out there.
Brian says, "Hey, I was at one of your seminars once, and you talked about calculated fields.
You said that you can't use a function in a calculated field." And she said, "I tried it and it works just fine." And turns out that she's right.
So we have here Sum of Revenue by Customer.
If I would need to build a function to calculate a new field-- so let's go to Pivot Table, Formulas, Calculated Field, and let's create something called Key Customer.
And here, the formula is going to be, =IF(Revenue>100000,1,0).
Now, the result here has to be numeric.
So we can't put a word like "Yes" or "Super Customer" or anything like that; we have to put ones and zeros.
Well, we click OK, and you see that we're going to get a brand new field.
And basically, as I drag this across, for the small Customers under 100,000 it shows up as 0; and for the large Customers it shows up as 1.
Now, what if we really want words to show up there?
Well, I'll go to that Sum of Key Customer and we'll click the Field Settings, and then I'll go into the Number format.
Now we're going to use something pretty obscure here: Custom Number format with zones-- Custom Number format with Zones.
And so what we're going to do, is say that if the number is positive, then we want to have the word "Yes" show up; if the number is negative, which it will never be, we can put nothing; and if the number is 0, then we'll put "New".
Basically, in this Custom Number format, because we have two semicolons, there's three zones.
The first zone is for positive, and the second zone is for negative, and the third zone is for 0.
So when I click okay, we apply that Custom Number format, and now I get the words "Yes" and "No" as the result in the Pivot Table.
So thanks to Brian, E. for sending in that note via Facebook saying, "Hey, it works just fine using Excel functions in the calculated field." And then make it a little bit better by using a Custom Number format to actually get that calculation to show us one bit of text or another.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Well, trust me, I am Bill Jelen.
You can't see me; we're battling the Sun here.
It's Akron, Ohio; it's not a sunny place but, for some reason, it's sunny.
And while the Sun is usually straight over there, and we can block it out fairly well with the wall, it's now gotten down to the south and so it's coming into the room and I'm all washed out.
But that's okay, still time for another cool Excel tip.
Talking about Pivot Tables today, and calculated fields in a Pivot Table.
Got of note from Brian, E. Brian, E. is, of course, in the UK.
If you're out at the MrExcel message board, you've probably seen Brian out there.
Brian says, "Hey, I was at one of your seminars once, and you talked about calculated fields.
You said that you can't use a function in a calculated field." And she said, "I tried it and it works just fine." And turns out that she's right.
So we have here Sum of Revenue by Customer.
If I would need to build a function to calculate a new field-- so let's go to Pivot Table, Formulas, Calculated Field, and let's create something called Key Customer.
And here, the formula is going to be, =IF(Revenue>100000,1,0).
Now, the result here has to be numeric.
So we can't put a word like "Yes" or "Super Customer" or anything like that; we have to put ones and zeros.
Well, we click OK, and you see that we're going to get a brand new field.
And basically, as I drag this across, for the small Customers under 100,000 it shows up as 0; and for the large Customers it shows up as 1.
Now, what if we really want words to show up there?
Well, I'll go to that Sum of Key Customer and we'll click the Field Settings, and then I'll go into the Number format.
Now we're going to use something pretty obscure here: Custom Number format with zones-- Custom Number format with Zones.
And so what we're going to do, is say that if the number is positive, then we want to have the word "Yes" show up; if the number is negative, which it will never be, we can put nothing; and if the number is 0, then we'll put "New".
Basically, in this Custom Number format, because we have two semicolons, there's three zones.
The first zone is for positive, and the second zone is for negative, and the third zone is for 0.
So when I click okay, we apply that Custom Number format, and now I get the words "Yes" and "No" as the result in the Pivot Table.
So thanks to Brian, E. for sending in that note via Facebook saying, "Hey, it works just fine using Excel functions in the calculated field." And then make it a little bit better by using a Custom Number format to actually get that calculation to show us one bit of text or another.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
