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: Tape It, iOS recording app for musicians Hello HN, Over the last 15 months, two friends and I developed the music recording app we felt we wanted based on our own needs as musicians. It's called Tape It [1] and has just recently hit the Apple App Store [2]. We put a lot of effort into a good UX to help musicians really focus on playing their instrument instead of pretending to be a recording engineer. The app records in stereo on newer iPhones (although that's a premium feature; the free version only records in standard mono audio quality). I would be really grateful for advice from this community on how to best approach marketing. We had a great TechCrunch article covering our launch [3], and we posted it on various music websites. Turns out advertising on Google or Apple Search is a dark art, though. We have some good ideas for developing a good social media presence, but they will take time. Please hit us with feedback, opinions and advice that you think a young ind...
Comments