Si

Simple method to create complex Excel formulas

Hacker News

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.

Visit product View on Hacker NewsFeb 19, 2022

Actual performance

14points

6comments

Made the leaderboard

Launch Intel predictions

Analyze your own launch →

Hacker NewsStrong engagement from HN community · Strong signals: io · Missing: https docs, excited, just released

56%56% predicted probability of success on Hacker News, based on ML models trained on real launch data.

nativeThis product was originally launched on this platform.

Indie HackersFits the IH revenue-focused audience · Missing: supports, reddit linkedin, podcasting

55%55% predicted probability of success on Indie Hackers, based on ML models trained on real launch data.

Product HuntUnlikely to reach the leaderboard · Strong signals: visual, using · Missing: mac, agents, macos

42%42% predicted probability of success on Product Hunt, based on ML models trained on real launch data.

TrustMRRLess likely to generate early MRR · Missing: mobile apps, ios, personal

39%39% predicted probability of success on TrustMRR, based on ML models trained on real launch data.

AppSumoMay struggle as an AppSumo deal · Missing: plus, platform, intuitive

26%26% predicted probability of success on AppSumo, based on ML models trained on real launch data.

Acquire.comPre-revenue stage for this audience · Missing: arr, mrr, revenue

20%20% predicted probability of success on Acquire.com, based on ML models trained on real launch data.

BetaListMay not resonate with beta-testers · Missing: web3, chat, crypto

1%1% predicted probability of success on BetaList, based on ML models trained on real launch data.

Correct prediction on native model

Similar products

Frockly – A visual editor for understanding complex Excel formulas62%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Frockly – A visual editor for understanding complex Excel formulas

SheetHub – Turn your Excel formulas into APIs64%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

SheetHub – Turn your Excel formulas into APIs

Onetap (sold)43%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Translate text into excel formulas

Indie Hackersvertical-ai

Generate drill-down report from formulas in Excel51%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Generate drill-down report from formulas in Excel

Melder - AI for Excel58%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Upgrade Excel with an analysis agent and AI formulas

Product Hunt+119Spreadsheets

Excel Formula Generator28%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Create formulas for excel & google sheets

Tracy57%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Trace Excel formulas faster

Product Hunt+106Productivity

SmartSideAi51%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Stop Googling Excel Formulas, Get the Right One in Seconds.

Indie Hackerscommitment-full-time

Vestimate59%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

Stop rebuilding Excel formulas. Start estimating faster.

Indie Hackers1government

QuickViz – Create complex charts, graphs and formulas using Markdown51%Launch Intel prediction score: how likely this product is to succeed on its source platform, based on its name, tagline, and description.

QuickViz – Create complex charts, graphs and formulas using Markdown

Browse all products Analyze your launch