3 ms·

Alternatives to Nested If Function

12 points by andsoitis 4mo ago

abstractspoon 4mo ago
In Excel
- jonasenordin 4mo ago
  Important_Distinction. _That_Should_Be_in_the_Headline_Maybe. Thanks, saving a click, not going for the article; not that it's necessarily bad or uninteresting; not that I wouldn't if I had more time on this planet.
  - jonasenordin 4mo ago
    Went ahead and clicked anyway, scientifically-minded that I am. No appreciable disillusionment occurred. Didn't Excel gain the ability to use names at some point? Is 'anominal' a word? As in 'anominal' or 'non-nominal' programming, or 'un-' or 'dis-'. Maybe Excel rises to 'pseudonominal'. --- quote --- Most users would use below nested IF function =IF(B2=$F$3,$G$3,IF(B2=$F$4,$G$4,IF(B2=$F$5,$G$5,IF(B2=$F$6,$G$6,IF(B2=$F$7,$G$7,IF(B2=$F$8,$G$8))))))
pavel_lishin 4mo ago
There was an excellent excel video by ... someone famous in our field whose name escapes me, and one thing I internalized from that was that VLOOKUP is always a bad choice, and INDEX + MATCH is almost always what you want instead. About once a year I go looking for that video, and about half the time, I find it, and half the time I don't. I should really bookmark it.
- imurray 4mo ago
  I think you're thinking of "You Suck at Excel" by Joel Spolsky. https://www.youtube.com/watch?v=JxBg4sMusIg https://www.youtube.com/watch?v=JxBg4sMusIg Lots of past HN discussion... https://hn.algolia.com/?q=you+suck+at+excel https://hn.algolia.com/?q=you+suck+at+excel That video was before the introduction of xlookup, which I believe is now worth considering over index and match.
  - pavel_lishin 4mo ago
    Yes! Thank you!
esafak 4mo ago
A benighted programming language if I ever saw one. I don't use Excel so maybe someone call tell me why people don't just migrate their queries to Python: https://support.microsoft.com/en-US/Excel/python/get-started-with-python-in-excel https://support.microsoft.com/en-US/Excel/python/get-started... Is support poor? Competitors can do it: https://www.quadratichq.com/ https://www.quadratichq.com/ (look at their VLOOKUP example)

12 points by andsoitis 4mo ago

abstractspoon 4mo ago

In Excel

jonasenordin 4mo ago
Important_Distinction. _That_Should_Be_in_the_Headline_Maybe. Thanks, saving a click, not going for the article; not that it's necessarily bad or uninteresting; not that I wouldn't if I had more time on this planet.
- jonasenordin 4mo ago
  Went ahead and clicked anyway, scientifically-minded that I am. No appreciable disillusionment occurred. Didn't Excel gain the ability to use names at some point? Is 'anominal' a word? As in 'anominal' or 'non-nominal' programming, or 'un-' or 'dis-'. Maybe Excel rises to 'pseudonominal'. --- quote --- Most users would use below nested IF function =IF(B2=$F$3,$G$3,IF(B2=$F$4,$G$4,IF(B2=$F$5,$G$5,IF(B2=$F$6,$G$6,IF(B2=$F$7,$G$7,IF(B2=$F$8,$G$8))))))

pavel_lishin 4mo ago

There was an excellent excel video by ... someone famous in our field whose name escapes me, and one thing I internalized from that was that VLOOKUP is always a bad choice, and INDEX + MATCH is almost always what you want instead. About once a year I go looking for that video, and about half the time, I find it, and half the time I don't. I should really bookmark it.

imurray 4mo ago
I think you're thinking of "You Suck at Excel" by Joel Spolsky. https://www.youtube.com/watch?v=JxBg4sMusIg https://www.youtube.com/watch?v=JxBg4sMusIg Lots of past HN discussion... https://hn.algolia.com/?q=you+suck+at+excel https://hn.algolia.com/?q=you+suck+at+excel That video was before the introduction of xlookup, which I believe is now worth considering over index and match.
- pavel_lishin 4mo ago
  Yes! Thank you!

esafak 4mo ago

A benighted programming language if I ever saw one. I don't use Excel so maybe someone call tell me why people don't just migrate their queries to Python: https://support.microsoft.com/en-US/Excel/python/get-started-with-python-in-excel https://support.microsoft.com/en-US/Excel/python/get-started... Is support poor? Competitors can do it: https://www.quadratichq.com/ https://www.quadratichq.com/ (look at their VLOOKUP example)