I recently helped my brother in law with a similar situation by converting a nested if statement to a lookup table. I also coached him to not keep variables on the page, not hidden in the formula. So in your example, I would have given the 20, 30 & the 10 variables their own cells. This way the values can be labeled, making it easier to understand for the next person to use the sheet to understand their purpose. This would also make it easier to update the sheet if, for example, the consignment rate changes.
@MyOnlineTrainingHub
Жыл бұрын
Yes, absolutely, John, variables are better in a cell or defined name. This tutorial was focused on deciphering the formula, so thanks for sharing that extra tip.
@craigsmall7984
Жыл бұрын
Coming from a programming background, the notion being able to exploit the numeric nature of a boolean really appeals to me - brilliant! And I thought I was pretty adept at writing formulas in Excel, but you have shown me some really excellent ways to improve the efficiency of my formulas - thanks so much!
@MyOnlineTrainingHub
Жыл бұрын
Awesome to hear, Craig!
@SergioAlejandroCampos
Жыл бұрын
Great Mynda. I love your accent.
@MyOnlineTrainingHub
Жыл бұрын
😊 thank you
@chrism9037
Жыл бұрын
I need an Advil after seeing those 15 nested IF formulas. Thanks Mynda!
@MyOnlineTrainingHub
Жыл бұрын
😁 I'm not gonna lie...it was not easy to decipher that formula.
@henryg5735
Жыл бұрын
Really good stuff - no if()s! But old school me still finds your late option of adding if() easier to understand - it immediately puts me on notice of a logical test, rather than having to figure it out from the multiplication by 1 or 0 method. YMMV
@MyOnlineTrainingHub
Жыл бұрын
Nothing wrong with using IF instead of the pure Boolean operations.
@GrainneDuggan_Excel
Жыл бұрын
Mynda, you took an overwhelming formula and made it readable. Just because we say 'if this then that', does not mean we have to use IF - good to remember! Thanks
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Grainne!
@TheOndricek
Жыл бұрын
Great tip, thank you Mynda! Some of the scariest nested IF formulas I've seen is when someone tries to "bucket" numerical data into categories. For instance you have a column with investment sizes which can range from $10 to $1B, but you need your output to be in categories of $0-$200M, $200M - $400M, $400M - $800M, and $800M - $1B. Many times that's solved by a monstrous nested IF, but I found using a VLOOKUP-True with a helper table off to the side was much more effective and efficient.
@MyOnlineTrainingHub
Жыл бұрын
Thanks, Nathan. Yes, the IF when you should have used a lookup is one of the biggest IF mistakes I see too.
@pc-doctor1416
Жыл бұрын
Impressive, I still have a lot to learn.
@MyOnlineTrainingHub
Жыл бұрын
Me too...the learning never ends with Excel 😊
@database_tips_tricks
Жыл бұрын
Great video , we all love people that love excel!
@MyOnlineTrainingHub
Жыл бұрын
Yes we do 😁
@Ugk871
Жыл бұрын
During internship in 2008 i used that extensive IF statement. Great tips
@adeohere
Жыл бұрын
Great. Thank you for this video lesson.
@MyOnlineTrainingHub
Жыл бұрын
You are welcome!
@lylestavast7652
Жыл бұрын
readability goes a long way in resolving issues - in all programming situations. Very interesting.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it, Lyle!
@Jallandhara
Жыл бұрын
Okay, this is one of the most amazing tools I've ever seen. Ever. Thank you so much for this demonstration!
@MyOnlineTrainingHub
Жыл бұрын
You're very welcome!
@courtn580
Жыл бұрын
just...brilliant!! Thanks for sharing Mynda!
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
@reng7777
Жыл бұрын
Wow you really have full domain of with this tool my friend!! love the clear way you explain to your audiance.. regards from Uruguay!!
@MyOnlineTrainingHub
Жыл бұрын
Glad you enjoyed it, Rene!
@teoxengineer
Жыл бұрын
There are so many benefits in this tutorial! Like using logical test gives same result as IF formula. And I’m using many times CTRL+H (Replace shortcut) to find wanted value and replace it correct value into a formula. There is no need to change every occurance by selecting one by one and it provides more easier to change values in one step. This tip from me is so useful Thank you Mynda
@MyOnlineTrainingHub
Жыл бұрын
Thanks, Emre! I agree, Find & Replace can be quicker, but it depends how complex the names you're replacing the old references with is. I just found in this case I could quickly go through the formula and replace them with the mouse faster than filling in the Find & Replace dialog box.
@wayneedmondson1065
Жыл бұрын
Awesome example and tips Mynda! Never thought of skipping IF and just using (boolean result of a simple formula test)*(number). Excellent!! Many thanks :))
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Wayne!
@GeertDelmulle
Жыл бұрын
Great lesson overall and great example of ‘mixed mode’ calculation (Boolean + arithmetic + automatic data type conversion). And maybe I should give that advanced editor a second look as well. Thanks!
@MyOnlineTrainingHub
Жыл бұрын
Glad you enjoyed it, Geert!
@darrylmorgan
Жыл бұрын
Hi Mynda!Awesome Tutorial,Really Great Solution Compared To That Scary Original Formula...Thank You :)
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it, Darryl!
@AshwaniSharma-fc2nz
Жыл бұрын
Very much helpful Maam
@silvo4880
Жыл бұрын
Cool video - subscribed. I like the add in approach, makes it very quick to interpret what’s going on. I’d been using evaluate on files with formula I inherit when it’s a mess, but that is a neater approach. And removal of the IF at the end was clean too. Liked.
@MyOnlineTrainingHub
Жыл бұрын
So pleased you liked it!
@colinellicott9737
Жыл бұрын
Great stuff thx Mynda. Apart from the expert information and presentation, I particularly like the way it sounds like you're about to start laughing at various points, cute.
@MyOnlineTrainingHub
Жыл бұрын
😁 Thanks, Colin!
@erdal4687
Жыл бұрын
Thanks
@MyOnlineTrainingHub
Жыл бұрын
My pleasure, Erdal!
@overdraftracing
Жыл бұрын
'Carriage Return' lovely... haven't heard that for years! does anyone even remember it's from typewriters? Thanks Mynda!
@MyOnlineTrainingHub
Жыл бұрын
I'm showing my age 😉
@JoseVazquez-qr9vj
Жыл бұрын
As always - great advice and presentation... Oddly, at about the 2:00 mark, when the formula in J11 is dragged down and then the results are supposed to match those in column I, the one for row 14 (Customer D) does not show the same results. Any ideas why? Was there a difference in the nesting, perhaps? (BTW, I haven't finished watching the video. Maybe it is mentioned at a later stage).
@JoseVazquez-qr9vj
Жыл бұрын
I now see that after the blank was removed form "consignment," the numbers matched.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it, Jose!
@tmb8807
Жыл бұрын
Nightmare formula. Good tips, especially the last bit about not necessarily needing to use IF for logical tests. Always forget that one myself.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
@LosoIAm
Жыл бұрын
I thought it was just me having "one hell of a head-ache" with the convoluted formula and I feel less dumb now, THANK YOU!
@MyOnlineTrainingHub
Жыл бұрын
😁 you're not alone!
@FarhanMerchant
Жыл бұрын
Great stuff ; very helpful
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Farhan!
@RandyMcNeil2
Жыл бұрын
Recently worked through a similar situation, Only thing that made it worse was the named cells were more like run-on sentences, and most were from a legacy set of variables so they really did not apply any longer to what it seemed to be saying. Argggg. 23 nested levels that all but one did the exactly same thing all the time, as the spreadsheet had morfed over the years.
@MyOnlineTrainingHub
Жыл бұрын
Yikes!
@plibralato
Жыл бұрын
thanks ... really inspiring.
@MyOnlineTrainingHub
Жыл бұрын
Great to hear!
@Homicidoll
Жыл бұрын
I love your videos, would you consider doing a video on visual date/charts in a dashboard setting for more fun things? Like for example, I've created a spreadsheet for movie tracking and I'd love to see more statistics based on the data but all the information I find online is mostly financial or business orientated. What about us weirdo's that spreadsheet for personal fun? I'd love the help if you ever end up making something. I feel a little lost beyond plopping boring charts down.
@MyOnlineTrainingHub
Жыл бұрын
Glad you enjoy my videos. Please see this dashboard tutorial for non-financial data: kzitem.info/news/bejne/sayirmRppKh4apg
@Faisal63045
Жыл бұрын
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
@MyOnlineTrainingHub
Жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@rusektor
Жыл бұрын
Well, this can be achieved with "Alt+Enter" which creates new line. You can move any part of the formula on the new line with this combination.
@MyOnlineTrainingHub
Жыл бұрын
Yes, I mention this as an alternative. The challenge for inexperienced users is knowing where one function ends and the next one starts. It's also much quicker to paste the formula in the Advanced Formula Environment.
@alanbraswell9905
Жыл бұрын
Can you help set up Excel for Day Trading or have you already put out a video?
@MyOnlineTrainingHub
Жыл бұрын
I have this tutorial: kzitem.info/news/bejne/2bCF3IFnb2Z5aYI I don't do consulting, Alan but I can refer you to someone if you'd like. Just get in touch via email: website at MyOnlineTrainingHub.com
@sachin.tandon
Жыл бұрын
V. Good!😀
@MyOnlineTrainingHub
Жыл бұрын
Thanks! 😃
@primaveranz
Жыл бұрын
@01:59 row 18 in columns I and J contains different values - why would simply formatting do this?
@primaveranz
Жыл бұрын
Ah I see it added a space to the end of "Consignment" ;)
@ExceluniForAll
Жыл бұрын
Ver good
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
@anurmertah3116
Жыл бұрын
Clever 🥰
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
@1974ANDRIUS
Жыл бұрын
🙂+🙄=😇
@MyOnlineTrainingHub
Жыл бұрын
😁
@mohammadbuhamad7744
Жыл бұрын
No sound
@MyOnlineTrainingHub
Жыл бұрын
Must be your end, Mohammad, as you're the only one to mention it and I have sound.
Пікірлер: 81