How To Convert Words and Names To Values in Numbers With IF, SWITCH and LOOKUP
HTML-код
- Опубликовано: 15 июл 2024
- macmost.com/e-2837 If you have names, product IDs or some other words and need to translate those to number values you can do it with the IF, IFS, SWITCH or LOOKUP functions in Numbers. While the first functions are good for small limited lists of values, only the LOOKUP function works well with a large list that changes often.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ RUclips - macmost.com/j-youtube (And hit the 🔔)
▶︎ RUclips Email Notifications - macmost.com/ytn ⭐️
▶︎ MacMost Weekly Email Newsletter - macmost.com/newsletter ✉️
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
00:00 Intro
01:05 The IF Function
03:44 Nested IF Functions
05:26 The IFS Function
06:29 The SWITCH Function
08:18 The LOOKUP Function
#macmost #mactutorial #numbers 
Наука
![Sean Rii, Karyon, Sharzkii - Taungule [My Love] (Official Music Video)](http://i.ytimg.com/vi/eJoMuypbSzQ/mqdefault.jpg)
I’ve searched all over and not found an answer. This is exactly what I’ve been looking for. Thanks
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!
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 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.
Very useful content as usual !
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.
VERY helpful! 👍😀
thank you very much.
Thanks bunches
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
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.
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.
Great +++
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 ?
In what world are Pears so cheap! ;) thanks Gary 🎉
👏👏👏👏👏
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.
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)
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.
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
@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.
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.
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
@@user-ll7in3nd6v You can do that with LOOKUP or VLOOKUP.. See this video and then start with that.
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.
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'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
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.
can you please help me I have forgotten my computer password
You'll need to contact Apple Support about that.