Intro going over table charts and formulas in keynote
and we're going to go over how to use formulas in keynote when you have tables and so on and
we're going to go to through a few examples and if you want to know how to format your table so it
looks nice just click the video in the corner and um that that video will show you how to make it
look nice like such if you're pasting it from google sheets or excel so now we're just going to
go over the formulas and um i want to show you a list and i'll put the link in the notes basically
this is a support guide information on keynote on apple website and it has a lot of functions
that it supports about 250 and it has the basic ones that you have in google sheets and um
excel and so on and if you click each of them you can see like the plus icon you can see more
detail what kind of formulas are listed and so most of the ones you will use in keynotes are
the basic ones some of the more complicated calculations you'll probably do in
google sheets or excel and anyway keynote is just to show like a summary of your
data so maybe some summary analysis and so so you were probably going to use like the basic
function which are like average sum round minimum maximum and so on and just simple calculation
adding subtracting calculating percentages whenever you paste information from like google
sheets or excel like it pastes as value so i actually like to use formulas in keynote when
i have any kind of tables because i just want to double check that while i was formatting or
changing things that i didn't make a mistake and then you're surprised that actually presenting
something and the data is incorrect there's a missing digit or what have you so i'm going to
start with this example let's calculate average total and sum of totals so let me actually
remove these i'm going to select these cells and AVERAGE, SUM start from the beginning so when i pasted the
data here it's a value data and let's say i want to calculate an average of this quarterly data
for car sales in 2021 for like six different car makers so you can just click on a cell start
typing equal and that's where the formula bar will show up and the formula bar is very small
and sometimes hard to tell what's going on so once if you hover over it the bottom line then
you have this like kind of arrow icon or this hand icon sometimes it doesn't want to show up right
away but i'll show you in a second or sometimes oh right there it's like a hand icon you can drag it
and expand this formula bar you can also move this away from your place because i don't like that i
don't see what's going on underneath so i like to actually position a little bit lower and now you
can start typing the formula so i will say average and now you can see google keynote is showing you
all the formulas that are available for you you can select one of them and i'm going to select
this one now it's asking you for your value and you can actually drag ranges just
as you drag in google sheets or excel i can just select these four columns and then
click enter now another way to see the sum if you click this check mark you can just click the check
mark and it will show you the average the result of your formula and once you have this formula you
don't have to redo it you can also copy it up you can do select the cell command c and select these
cells and command v you can do it in such a way Copy & Paste Formulas or you can also take this and you will have this
like little yellow dot or circle in the middle of your cell you can actually drag this to copy
over the formula this way and you can double check how if the formula is showing the correct
information if you just double click on it it will show you what it's calculating now there's
two also other ways to see the formula like let me actually um try to move this away from here
you can click on this arrow next to the function icon and you can say show formula as text so you
see the way it's presented right now it's like this in these boxes uh formula and then the range
i can if i do show formulas text it's actually presenting it more like it does usually in excel
or google sheets and then you can also do convert formula to text in a cell so that would do is
actually show you the calculation like this but it's not the calculation itself so um that's
the difference so i'm going to change this to have an actual formula here so once i calculate
the average i see the numbers here and i worry about you know changing the format because when i
copy it over i must have let me just see my table table i didn't use the alternating row because if
i do that i actually can make this color copy over across the row so i'll just do this so so then
even if i copy the formula let me show you um if i copy this formula now it will not change
modify from the white cell it won't modify my column and my rows that are like the light blue
because it's alternating row color that's the way to do it um to you know avoid if you
have tables with formulas i suggest to use the alternating row color because if you're
copying it over you will come across this issue a lot now let's do the sum so sum is equal
and then i will just use sum and you also have this option sum if you can do some products some
other formulas here but i'm just going to do some and i'm okay let me do shove in function browser
oh you can also see right now here it's like the form from function help menu so you can have
some help on your formulas right here as well and you can also do insert function here as
well but okay let me do one more time equal sum parenthesis and i'm going to change
it to as text i just like it better and i'm going to add these
four columns close parentheses enter and then again i'm going to copy
it over like so now i also want to enter a formula that adds up total for all the six car
makers so i'll just do at the bottom equal sum and then just select the range and so so this is a
quick way to do some of these and i can also maybe create a round like i can add a round function
so as if in excel like that's why i kind of like ROUND & Nested Formula using the show formulas text because i can just
it's easier for me to type other formulas here so i'm going to do round select
this comma and then how many digits maybe let me do two two four digits i can
also add nested function within the formula in keynote let's do another example so this one
is an interesting one let's say you have some kind of you know financial presentation of stocks and
pricing and maybe you know you're working on this presentation over time like you don't want to go
back and have to pull this data for these stocks STOCK Price Formula uh maybe the presentation you started two
months ago so you want to keep updating it so this is actually a good option which is
the formula that you can use in keynote stock then you you put the symbol and then it's
optional value and now let me show you let's go to the formulas like here it would tell
me more information on the syntax and what kind of information i can ask for in the formula and you
can see the formula right now it's stack which is you have to provide a symbol and then um price
this price actually has multiple options you can say 0 1 2 3 4 and so on there's a list of how many
25 different things you can pull for this stock and each of these numbers represent like either
price name change and etc okay now you see this you must specify a value so this will be an error
like whenever you see something like this it's an error and that means the formula is something is
wrong with the formula so i'm going to change this okay so now let's start typing our formula
here so let's say i'm going to take this so if you want to delete the formula just select
and delete basically you can do this no i don't want to do this but i can just do right click
on the cell and clear all so i can delete the formula sometimes it didn't work doesn't work
if i just try to oh it did work this one but sometimes it doesn't want to like delete for some
reason just goes to the formula even though i'm the x just makes basically saying cancel changes
and close so it's not really deleting the formula if let's say if i do this and click check mark
then i'm deleting the formula okay equals stock parentheses and now i'm going to reference this
and now i'm going to also select which option so name i have to i have to check what's name name
what number do i have to put for name for name it's one i have to put one close parenthesis and
enter and now i have the name now if i want to copy this over like we were talking about and
then do you see here i put the the names of the options that i have to select just to reference
quickly so i don't have to look for it in the um guide so let's say if i have this and
now i once i have this formula and i want to copy it over like so okay i have all kinds
of errors why because actually here if i double Relative & Absolute References check this formula i can see what is happening
it's actually referencing b three so it's not referencing the symbol but it's referencing
the name so i actually have to select b2 in my formula and make it an absolute reference
so i'm going to go back to my original formula and here i'm going to say preserve row and column
i just want to preserve the row because column actually i'm going to be later on copying this
formula across so i want to preserve rows so these are the options for absolute reference and
now if i click enter and now if i copy it over it is working but now i want to change what
i'm referencing so i want to not do the name but you see you can actually click on here
and it shows you what options do you have for this formula price and then i'm going to do
the same thing here i want a volume which was see let me just actually move this value
volume was eight then high 52 week high it's 11 and 52 low that's 12. annual dividend that's number 20. so i
already kind of went through it so i know okay so now in here it's not available i'll show you what to do in case you have
an error and earning per share 21 IFERROR Formula and finally p e ratio 20 3. so whenever you have this
and you might have an error you can also add an if error nested formula
beforehand so i'm going to say if error what to do maybe put not available and that's
what happens and now i can actually copy this and you have this like yellow dot in the middle
you can drag this across and it will copy for all the other uh tickers and symbols that you have for
each of the companies so you know it's a quick way of referencing and and using the stock formula as
well as if an error formula if you have errors in your calculations here's another example that we
have we have average home prices and i want to do an average and we already did the average
calculation before let me see if okay you see this is not a formula because i paste
it over so i can just use average formula for each of the regions and then i can copy the
formula you see it's very sensitive like when you click anything it's very sensitive i can just
click the x not to change anything in my formula and copy the formula over now um percentage change
so that's another one that you might um be using Calculate Percentages a lot so let me delete this and show you how to
calculate that so let's actually make this box a little bit bigger if i can select this okay
so what we want to do uh percentage change is basically taking the new value subtracting the
old value and divided by old value so i'm going to use parentheses because i want to make sure
that my arithmetic is correct saying the new value for 2022 subtract the old
value close parentheses and divide okay it's the division yes when i click the slash
it does the uh division sign like mathematical division sign and then divided by 2021 the old
value click press and then you have the collect formula and then we just copy it across whenever
you click on a cell you can see it shows you what is it using which cells it's referencing another
one quick one here this one is just basically using a sum of these total car cells by region
across so that's that's something we've ever done finally this is a final example in our video
that we're going to go over it's kind of like Calculating Tax Example using a table and i have a table here and
um it is using a table but i removed all the formatting so you can't tell but it's a it's
like a clean presentation of the calculations if you ever want to lock it like you know because
whenever you adding some other formatting you want to make sure you lock it so you don't
make some inadvertent changes to your table but right now because i'm going to put
formulas i want to show you how to do that so i have basically tax calculation for 2022 and
i have a single taxpayer with taxable income of hundred thousand and the brackets they're they're
i'm just showing some of the brackets that are applying because there are also more brackets for
each taxpayer but right now i'm just showing at 10 12 22 24 and it's saying you know the
income between 0 and 10 000 it's taxed at 10 between ten thousand and forty one and twelve
percent and so on so it's like a progressive um tax system that basically the tax rate
increases as your income increases and just wanna show you that you're not finding your
highest tax bracket so if this person is in a 24 bracket you're not going to take um 100 000 times
the 24 as uh you know and the tax rate will be 24 24 000 you're actually going to have to calculate
at each tax rate how much you pay in taxes so we're going to do that so amount of income
at each so what i want to do is basically say how much of income at this tax bracket would
be in total so i'm taking the maximum value subtracting the minimum so you're using
subtraction now later on i can take this yellow dot and copy over the formula now for the
last item i have to change the formula because now it's taking the maximum 170 000 subtracting
89 but i actually have 100 000 not 170 so i can just actually click on the here and remove
this item select the cell that i want and click and check to enter it and now i have this
is the income level at each of the brackets there will be tax at each of the brackets so the
corresponding bracket so now i want to multiply by tax rate so i can say equal select now i
can just move this may be somewhere else a little bit because i can't tell what's happening
i like to be able to tell what's happening take this amount at each each of the bracket rate
multiply by tax rate and press enter or check and now i'm going to drag this formula down and
now this is total for each of the tax brackets that i will be paying so i'll be paying two
thousand six hundred and twenty two at twenty four percent and then uh twenty two at twenty two
percent i'm paying ten thousand and each and a ten percent i'm paying 1000 and so on and each of
them i want to add them up so i'm going to do some and i'm going to select the cells and enter so
right now it shows me okay so i'm actually going to be paying taxes 17 800 versus if you just did
a plane calculation if this was not a progressive increasing tax rates tax system so this is like a
quick way to basically show an example and i can also select the table and some of these formatting
that i've done and lock it so even if i change anything or move anything i'm not affecting
my calculations so you want to make sure you when you do the calculation lock your table so
nothing can be changed by an accident as you can see it's very sensitive so sometimes that
can happen so thanks so much for watching you