Skip to main content

Show HN: Simple method to create complex Excel formulas https://ift.tt/47qv6CL

Show HN: Simple method to create complex Excel formulas If I have trouble visualizing an excel formula in one cell on the fly, I use a trick to make it easier. Let's say I have the following cells | A | B | C | 1|mary| |Jane| In D1 I want to concatenate the cell values if the cell contains text. First I make a formula to check if the cell contains text somewhere in a cell on the sheet. Let us go with A6. A6: =ISTEXT(A1) result=TRUE ; Hooray! Then, if A6 is true, I want to display the text from A1 because I cannot concatenate "true" as I will be doing later on: A7: =IF(A6=true,A1,"") result=mary ; Yippy! I do the same thing for each cell: A8: =ISTEXT(B1) result=FALSE ; Sweet! A9: =IF(A8=true,B1,"") result=blank ; Thank goodness! A10: =ISTEXT(C1) result=TRUE ; Sweet! A11: =IF(A10=true,C1,"") result=jane ; Thank goodness! I know I am going to ultimately combine them with concatenate like so: A12: =CONCATENATE(A7," ",A9," ",A11) result=mary jane Right now it is a mess, but it is easy to follow and create each formula. Now I just copy the formula from the correct cell into the final concatenation (A12) To start, I will replace "A7" in the A12 formula with the formula from A7 minus the "=" sign: A12: =CONCATENATE(IF(A6=true,A1,"")," ",A9," ",A11) result=No change ; Perfect! I continue that process with A9 and A11 in cell A12 formula to get this: A12: =CONCATENATE(IF(A6=1,A1,"")," ",IF(A8=1,B1,"")," ",IF(A10=1,C1,"")) result=No change ; 100% success so far! Now I keep copying the referred cells with formulas(A6, A8, & A10) until I have only the cells with data left(A1, B1, & C1) in the A12 formula: A12: =CONCATENATE(IF(ISTEXT(A1)=1,A1,"")," ",IF(ISTEXT(B1)=1,B1,"")," ",IF(ISTEXT(C1)=1,C1,"")) result=No change ; Phew... Plug that formula from A12 into D1 and it is finished. Using this method, I find it very easy to work out more complex formulas. I wish I had figured this out on day 1. February 19, 2022 at 11:11AM

Comments

Popular posts from this blog

Show HN: Launch VM workloads securely and instantaneously, without VMs https://ift.tt/2QwJ1Kd

Show HN: Launch VM workloads securely and instantaneously, without VMs Hello HN! We've been working on a new hypervisor https://kwarantine.xyz that can run strongly isolated containers. This is still a WIP, but we wanted to give the community an idea about our approach, its benefits, and various use cases it unlocks. Today, VMs are used to host containers, and make up for the lack of strong security as well as kernel isolation in containers. This work adds this missing security piece in containers. We plan on launching a free private beta soon. Meanwhile, we'd deeply appreciate any feedback, and happy to answer any questions here or on our slack channel. Thanks! April 29, 2021 at 07:50AM

Show HN: Comment on live websites just like you comment on Google Docs/Figma https://ift.tt/GRhrjX0

Show HN: Comment on live websites just like you comment on Google Docs/Figma I'd love your feedback on this new JS plugin we launched. With this, you can comment on live websites just like you comment on Google Docs or Figma. You can use is to get Copy or UI feedback right on the website you are building. Feedback can be provided in rich formats like audio and video. You can get started by installing a JS tag in the footer of the website. You can then turn the review mode on or off on demand by adding “?review=true” to the URL. Demo video (43s): https://www.youtube.com/watch?v=cdnfBEw8TfI Demo video: https://www.youtube.com/watch?v=h6vxzXJuh8o https://ift.tt/ocLpdEu October 26, 2022 at 02:18AM