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
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
Comments