I have watched a number of your videos after my generic search pointed me to your channel and after watching a few, a BIG THANK YOU! They are so easy understand and your real life examples are perfect! I'm now a subscriber and can't wait to watch others. Keep up the great work! These have been so helpful.
I love your straight forward tutorials. You are doing a great job. With the lookup function, perhaps you could have added some information about how to deal with the potential issue where there is no match in the lookup and the differences between hlookup, vlookup and xlookup but I guess that can be part of another video.
Great video Gary! Do you have a video that covers sort of a reversed input? I have to enter repetitious data into numbers to be calculated and the way I currently do it is by separating the data M1-M20, then entering the dollar value in each column. What I’m looking for is a way for me to type in M(X) and the dollar value,and have automatically imputed in a designated cell. If M1, then cell A1 but cell A1 not 0 then A2, but if M5 then E1…..?
Hi Gary - great videos - thank you. How to sum a number of data entries within a date range - like expenses in January. I'm using SUMIF to collate payment types but how to capture the month (without using a pivot table)? Thank you
Have another column that just holds the month, extracted from the date column using the right function. Then use SUMIF with that column as the condition.
Hi, very nice and informative videos, I have a question about your convert function video for things like distance and such. I am seeking the symbols for values of energy like “Gigatons”, I was wondering if there is such a thing for that value to convert from joules to Gigatons. Thank you.
@@MW2proification I can't seem to find any mention of a symbol for "gigaton." Only the letters Gt. Even ChatGPT doesn't know what symbol you mean. Do you have a source, like a web page, that shows this symbol?
I can see this being useful for a construction project; something with multiple prices from different retailers. or possibly different lengths of boards; or say, different lengths of screws. Thanks! Lookup... Cool!
Great videos. Question, I am working with a musical scale spreadsheet in which I am programming certain steps. When going half a step or semitone I am using "&" with the corresponding sharp "#" or flat "b". But how can I increment for example from a "C" to a "D", by using a lookup table perhaps, or is there a simpler way? Thanks in advance!
@@macmost Thx. Yes, that is the most basic case I was asking about, but each musical scale has different number of steps, which is the eventual goal. The lookup table example above gave me an idea which I expanded on & it seemed to have worked to populate a whole spreadsheet with the musical notes of a variety of major & minor western music scales.
@@macmost yes it is for printing my invoice to the client / even filling bank docs ,.. that’s why . Is there a better way to do that ! ? Pls enlighten me .
@@sacvasanth You can use a function in my ClipTools app to do it manually each time. But if it is an invoice or bank documents, wouldn't a real number work fine? I can only think of checks as being the only place you really need it.
Hey Gary, great video. Is there a way to add the word values in a single cell (the product cell) and have the sum in another cell (the total) using LOOKUP? Thanks
I'd like to assign a number value to a word, as in: Apple = .10, Peach = .15, etc... and use one cell to "add" the words (Apple + Peach) and have the total calculated in another cell. I understand that this function doesn't likely exist, but thought i'd ask. @@macmost
@macmostvideo Gary, with the Lookup function, what if I have in your example price to be "Text", "Email" and Blank field, Blank gives me an error. I'm making a list of people and their preferred method of communication on a 3rd column in my spreadsheet.
@@macmost Thanks for replying so quickly. I ended up using ISBLANK function in my formula and got it to work. IF(ISBLANK(LOOKUP(F7,Name,'Names & Where to bring'::text or email)),"",LOOKUP(F7,Name,'Names & Where to bring'::text or email))
It finds the closest text match in the Product column and returns its Price. The XLOOKUP function has parameters that can be used to find only exact matches.
Hi Gary, I am a new convert to macbook and your videos are extremely helpful... Thank You Is there a way to transfer/import Firefox/Chrome bookmarks from a wondows PC to Macbook?
Gary, Thanks so much for your videos. I created a simple sum automation with copy and paste using Quick Action in the automator. However, it runs at real time speed, taking more than 7 seconds to complete. Is there a way to make this script run instantaneously and is it possible to assign quick keys to it? Thanks!
@@macmost Sorry. I'm using the automator (Quick Action) to sum a selection of numbers, then copy the resulting number and paste it elsewhere. I've used the "Playback Speed" slider to 10x the speed, but it still takes more than 7 seconds to complete the task. I created the Quick Action script by Recording (Watch Me Do) my actions. Thank you.
@@garrykim9098 Don't use Automator to do that. Just select the cells and you'll see the sum at the bottom of the Numbers window. Or, in the cell where you want the result create a formula to do it.
These short tutorials are brilliant I love Apple numbers and pages Having watched this and the check box video I have a simple question can you use a check box as a selection tool for a list then use the look up table function to produce a list For example I need to create a fast way to quote for electrical jobs So my checkbox element would allow me to quickly select product Then use this selected list list to total up a price based on values in the look up table that I can adjust when pricing changes Wow sorry for the long message but have tried for a few hours and can’t seem to get it to work hahaha Any help would be truely Helpful and very much appreciated 😊😊
Yes. But it gets complicated. Not the sort of thing I can explain in a RUclips comment. Start with ruclips.net/video/WBsLKvCUS8c/видео.html and learn some more and go from there.
These videos are so helpful...this is what I was looking for but now have to do a little deeper dive. What if the LOOKUP is searching for a range? As in, if between 1-1000 then A, if between 1001-2500 then B, if between 2501-5000 than C, etc.
That worked...thanks for the help... If I run into another issue, I will return to your videos. IFS(EXP.::A1≥324000,25,EXP.::A1≥299000,24,EXP.::A1≥275000,23,EXP.::A1≥252000,22,EXP.::A1≥230000,21,EXP.::A1≥209000,20,EXP.::A1≥189000,19,EXP.::A1≥170000,18,EXP.::A1≥152000,17,EXP.::A1≥135000,16,EXP.::A1≥119000,15,EXP.::A1≥104000,14,EXP.::A1≥90000,13,EXP.::A1≥77000,12,EXP.::A1≥65000,11,EXP.::A1≥54000,10,EXP.::A1≥44000,9,EXP.::A1≥35000,8,EXP.::A1≥27000,7,EXP.::A1≥20000,6,EXP.::A1≥14000,5,EXP.::A1≥9000,4,EXP.::A1≥5000,3,EXP.::A1≥2000,2,EXP.::A1≥0,1)
Hello. in your video "Creating Dynamic Pop-Up Menus In Numbers With AppleScript (#1703)", you create a popup menu with a script. it's work! thank you. But, is it possible to use scripts to make imbricated popup menus ?
@macmostvideo, I don't know if you noticed this. But my extension on third party apps is blocked and I don't know how to allow it to lunch. I would be glad with you make a video go it thanks you.
@@montasralkady You don't need a macro or script or anything for that. You can just select the text inside the cell, and you use Edit, Speech, Start Speaking. Or, go to System Settings, Accessibility, Spoken Content and turn on Speak Selection. Click on the "i" button there and check the keyboard shortcut. Now you can use that with a cell selected.
i'd like to ask if there is any function by which we can :- Return Multiple Match Results for example bill no 1 has 5 items apple, mango, berry, kiwi & blue berry Qty 2,5,7,3&8 accordingly and list goes till bill no 128. how do we get bill no wise stock without hiding rows/column
Thank you Gary, but I have one issue with Lookup table. If you change the price in the lookup table then all the corresponding prices in the Sales table change. That is not what you want to do because on the earlier dates you haven't sold those with the new price. The new price should only apply from the date the price was changed.
Or could you have it do a lookup by date and name? So if it apple before a certain date, then it would be one price but if it’s after that date, it’s a different price
Would it be possible to do a video on System Preferences/Internet Accounts/ then what a person may or may not effect when they choose to check mark or leave unchecked, aka mail, contacts, calendars, etc, does this have to do with syncing with your other devices, especially if you are under the iCloud part the top icon in the list, where my other email addresses are. I hope you can understand what I am asking. This came about, when I was trying to figure out, why, my son and daughter, have multiple present icons listed on their birthdate in calendar. I even tried clearing their birthdays from their Contact card, Frustrated, I just unchecked birthdays, on the left side of calendar, and made my own birthday event for each. I understand if you can't, TIA
Under iCloud, those are whether you use your iCloud account for those apps (you should). As for birthdays, those probably come from your Contacts app records.
I have watched a number of your videos after my generic search pointed me to your channel and after watching a few, a BIG THANK YOU! They are so easy understand and your real life examples are perfect! I'm now a subscriber and can't wait to watch others. Keep up the great work! These have been so helpful.
I’ve searched all over and not found an answer. This is exactly what I’ve been looking for. Thanks
A very useful and informative video tutorial today! Thank you, Gary! 👏❤️
Wow! I'm going to spend all day looking for ways to use this new fun knowledge...and I have so much else I need to be doing! Damn you, Gary!
I love your straight forward tutorials. You are doing a great job.
With the lookup function, perhaps you could have added some information about how to deal with the potential issue where there is no match in the lookup and the differences between hlookup, vlookup and xlookup but I guess that can be part of another video.
Great video Gary! Do you have a video that covers sort of a reversed input? I have to enter repetitious data into numbers to be calculated and the way I currently do it is by separating the data M1-M20, then entering the dollar value in each column. What I’m looking for is a way for me to type in M(X) and the dollar value,and have automatically imputed in a designated cell. If M1, then cell A1 but cell A1 not 0 then A2, but if M5 then E1…..?
Sounds like you want a LOOKUP table. See ruclips.net/video/JMYHK5FZUjU/видео.html
Very useful content as usual !
I will apply this function in my sheets,thank you so much
Apply in what way?
@@macmost I tried to use SWITCH in estimates instead of IF function
Hi Gary - great videos - thank you. How to sum a number of data entries within a date range - like expenses in January. I'm using SUMIF to collate payment types but how to capture the month (without using a pivot table)? Thank you
Have another column that just holds the month, extracted from the date column using the right function. Then use SUMIF with that column as the condition.
@@macmost Many thanks Gary!
Hi, very nice and informative videos, I have a question about your convert function video for things like distance and such. I am seeking the symbols for values of energy like “Gigatons”, I was wondering if there is such a thing for that value to convert from joules to Gigatons. Thank you.
Not sure what you are looking for. The character symbol you would type? Or the formula to convert one amount to another?
@ The character symbol for values like “Gigaton”, I got the understanding of converting amount to another thanks to your videos.
@@MW2proification I can't seem to find any mention of a symbol for "gigaton." Only the letters Gt. Even ChatGPT doesn't know what symbol you mean. Do you have a source, like a web page, that shows this symbol?
@@macmost I guess the closest with “Tons of TnT” with the symbol “t”.
en.m.wikipedia.org/wiki/TNT_equivalent#Historical_derivation_of_the_value
@@MW2proification Sorry, I don't understand. I don't see any special characters shown on that page.
I can see this being useful for a construction project; something with multiple prices from different retailers. or possibly different lengths of boards; or say, different lengths of screws. Thanks! Lookup... Cool!
Great videos. Question, I am working with a musical scale spreadsheet in which I am programming certain steps. When going half a step or semitone I am using "&" with the corresponding sharp "#" or flat "b". But how can I increment for example from a "C" to a "D", by using a lookup table perhaps, or is there a simpler way? Thanks in advance!
I can't really tell what you are doing from that description. Why not just enter the notes as text: C, C#, D, D#, etc.?
@@macmost Thx. Yes, that is the most basic case I was asking about, but each musical scale has different number of steps, which is the eventual goal. The lookup table example above gave me an idea which I expanded on & it seemed to have worked to populate a whole spreadsheet with the musical notes of a variety of major & minor western music scales.
VERY helpful! 👍😀
Great video. Numbers needs a TEXT function to make it simpler to convert values to text.
...I'm honestly amazed it doesn't. TEXT seems like a necessity.
Hi Garry ! How to convert numerical values to words in NUMBERS SPREADSHEET ? Kindly guide us.
There's no easy way to do that. Are you looking to do it for a number here and there, or for a large collection of numbers?
@ It’s here an there. To be more specific… Only in one column for my bills.
@@sacvasanth But why would you want to convert numbers to words in data like that? Usually, you only do it if printing a check or something.
@@macmost yes it is for printing my invoice to the client / even filling bank docs ,.. that’s why . Is there a better way to do that ! ? Pls enlighten me .
@@sacvasanth You can use a function in my ClipTools app to do it manually each time. But if it is an invoice or bank documents, wouldn't a real number work fine? I can only think of checks as being the only place you really need it.
Hey Gary, great video. Is there a way to add the word values in a single cell (the product cell) and have the sum in another cell (the total) using LOOKUP? Thanks
Not sure what you are asking here. If you want a sum, then use the SUM formula. You wouldn't use LOOKUP for that.
I'd like to assign a number value to a word, as in: Apple = .10, Peach = .15, etc... and use one cell to "add" the words (Apple + Peach) and have the total calculated in another cell. I understand that this function doesn't likely exist, but thought i'd ask. @@macmost
@@MACOGasServices You can do that with LOOKUP or VLOOKUP.. See this video and then start with that.
@macmostvideo Gary, with the Lookup function, what if I have in your example price to be "Text", "Email" and Blank field, Blank gives me an error. I'm making a list of people and their preferred method of communication on a 3rd column in my spreadsheet.
Not sure what you are asking. Is it giving an error because the lookup doesn't match anything? Or is it that you can't get the formula right?
@@macmost Thanks for replying so quickly. I ended up using ISBLANK function in my formula and got it to work.
IF(ISBLANK(LOOKUP(F7,Name,'Names & Where to bring'::text or email)),"",LOOKUP(F7,Name,'Names & Where to bring'::text or email))
@@macmost Gary, got a quick one for you. How do I "Merge Menu Items" in my Pop-up Menu on my iPad's Numbers?
@@jakeman1968 If you just grab all of the cells in the new range and re-apply the Format it seems to work. Play around with it.
Thanks bunches
Thanks for the interesting video. Why the value for pear was 0.12 first and not an error?
It finds the closest text match in the Product column and returns its Price. The XLOOKUP function has parameters that can be used to find only exact matches.
@@alanmclean1399 Thank you very much
Hi Gary, I am a new convert to macbook and your videos are extremely helpful... Thank You
Is there a way to transfer/import Firefox/Chrome bookmarks from a wondows PC to Macbook?
Yes. I think both of those use cloud systems of their own and you can sign into the same account on Firefox/Chrome on your Mac to see your bookmarks.
@@macmost I think they were talking about importing the bookmarks into Safari on on the Mac.
Gary, Thanks so much for your videos. I created a simple sum automation with copy and paste using Quick Action in the automator. However, it runs at real time speed, taking more than 7 seconds to complete. Is there a way to make this script run instantaneously and is it possible to assign quick keys to it? Thanks!
Not sure what you are doing here. Why did you need to use Automator to sum amounts in Numbers? That's what formulas in Numbers are for.
@@macmost Sorry. I'm using the automator (Quick Action) to sum a selection of numbers, then copy the resulting number and paste it elsewhere. I've used the "Playback Speed" slider to 10x the speed, but it still takes more than 7 seconds to complete the task. I created the Quick Action script by Recording (Watch Me Do) my actions. Thank you.
@@garrykim9098 Don't use Automator to do that. Just select the cells and you'll see the sum at the bottom of the Numbers window. Or, in the cell where you want the result create a formula to do it.
thank you very much.
These short tutorials are brilliant I love Apple numbers and pages
Having watched this and the check box video
I have a simple question can you use a check box as a selection tool for a list then use the look up
table function to produce a list
For example
I need to create a fast way to quote for electrical jobs
So my checkbox element would allow me to quickly select product
Then use this selected list list to total up a price based on values in the look up table that I can adjust when pricing changes
Wow sorry for the long message but have tried for a few hours and can’t seem to get it to work hahaha
Any help would be truely Helpful and very much appreciated
😊😊
Yes. But it gets complicated. Not the sort of thing I can explain in a RUclips comment. Start with ruclips.net/video/WBsLKvCUS8c/видео.html and learn some more and go from there.
These videos are so helpful...this is what I was looking for but now have to do a little deeper dive. What if the LOOKUP is searching for a range? As in, if between 1-1000 then A, if between 1001-2500 then B, if between 2501-5000 than C, etc.
I guess this is a case for IFS...if >384000 than Z, if >359000 than Y, etc.?
LOOKUP will handle that. Read the function description for LOOKUP and you'll see how to set it up.
That worked...thanks for the help... If I run into another issue, I will return to your videos.
IFS(EXP.::A1≥324000,25,EXP.::A1≥299000,24,EXP.::A1≥275000,23,EXP.::A1≥252000,22,EXP.::A1≥230000,21,EXP.::A1≥209000,20,EXP.::A1≥189000,19,EXP.::A1≥170000,18,EXP.::A1≥152000,17,EXP.::A1≥135000,16,EXP.::A1≥119000,15,EXP.::A1≥104000,14,EXP.::A1≥90000,13,EXP.::A1≥77000,12,EXP.::A1≥65000,11,EXP.::A1≥54000,10,EXP.::A1≥44000,9,EXP.::A1≥35000,8,EXP.::A1≥27000,7,EXP.::A1≥20000,6,EXP.::A1≥14000,5,EXP.::A1≥9000,4,EXP.::A1≥5000,3,EXP.::A1≥2000,2,EXP.::A1≥0,1)
The most valuable part was unexpected-I've used Numbers for years but never knew you could double-click on a column to exclude headers/footers.
Hello. in your video "Creating Dynamic Pop-Up Menus In Numbers With AppleScript (#1703)", you create a popup menu with a script. it's work! thank you. But, is it possible to use scripts to make imbricated popup menus ?
@macmostvideo, I don't know if you noticed this. But my extension on third party apps is blocked and I don't know how to allow it to lunch. I would be glad with you make a video go it thanks you.
Not sure what you are asking and how this applies to these Numbers functions.
how to make macro in numbers?... thanks
What is it you are trying to DO?
@@macmost thanks for replying , i want to write a macro as in excel
@@montasralkady Right. What would this macro DO?
@@macmost a function to speak a cell text
@@montasralkady You don't need a macro or script or anything for that. You can just select the text inside the cell, and you use Edit, Speech, Start Speaking. Or, go to System Settings, Accessibility, Spoken Content and turn on Speak Selection. Click on the "i" button there and check the keyboard shortcut. Now you can use that with a cell selected.
In what world are Pears so cheap! ;) thanks Gary 🎉
i'd like to ask if there is any function by which we can :- Return Multiple Match Results for example bill no 1 has 5 items apple, mango, berry, kiwi & blue berry Qty 2,5,7,3&8 accordingly and list goes till bill no 128. how do we get bill no wise stock without hiding rows/column
I demonstrate something like this here: ruclips.net/video/r_RRpZhUCzQ/видео.html
@@macmost thank you sir
Thank you Gary, but I have one issue with Lookup table. If you change the price in the lookup table then all the corresponding prices in the Sales table change. That is not what you want to do because on the earlier dates you haven't sold those with the new price.
The new price should only apply from the date the price was changed.
Right. In that case, a LOOKUP table is no what you want. You want to have a fixed price with each record (row) in your table.
Or could you have it do a lookup by date and name? So if it apple before a certain date, then it would be one price but if it’s after that date, it’s a different price
Would it be possible to do a video on System Preferences/Internet Accounts/ then what a person may or may not effect when they choose to check mark or leave unchecked, aka mail, contacts, calendars, etc, does this have to do with syncing with your other devices, especially if you are under the iCloud part the top icon in the list, where my other email addresses are. I hope you can understand what I am asking. This came about, when I was trying to figure out, why, my son and daughter, have multiple present icons listed on their birthdate in calendar. I even tried clearing their birthdays from their Contact card, Frustrated, I just unchecked birthdays, on the left side of calendar, and made my own birthday event for each. I understand if you can't, TIA
Under iCloud, those are whether you use your iCloud account for those apps (you should). As for birthdays, those probably come from your Contacts app records.
Great +++
👏👏👏👏👏
can you please help me I have forgotten my computer password
You'll need to contact Apple Support about that.