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 01:11AM