Free AI bot that gives you the Excel formula based on a given prompt/sentence
Content link | Reddit Link
r/InternetIsBeautifulu/dabressler (10596)

Comments (358)

Here's a somewhat simpler (but still tricky) problem it didn't solve:

Return the longest string in column A

It answered:

=MAX(LEN(A:A))

Which finds the length of the longest string in column A.

The actual longest string in column A could be found by:

=INDEX(A:A, MAX( ROW(A:A) * (LEN(A:A) = MAX(LEN(A:A))) ) )

Edit: some people have asked for explanation on this formula. I wrote an answer in a response, and have copied it below for more visibility.

=INDEX(A:A, MAX( ROW(A:A) * (LEN(A:A) = MAX(LEN(A:A))) ) )

The calculation happens from the inside out.

First, expand all the A:A as arrays of cells that these functions act on.

LEN() acts on a single cell, so it acts on each cell individually. LEN(A:A) creates an array of numbers, with a value in each cell equal to the length of the string in the corresponding cell in A:A.

ROW() also acts on a single cell, so it creates an array of numbers with values equal to their row number (ie, 1 2 3 4 5 6 etc).

MAX() acts on an array, so when given LEN(A:A), it creates a single value, the length of the longest string in the array.

So when we have:

LEN(A:A) = MAX(LEN(A:A))

We compare an array (on the left) to a single value (on the right), which creates an array of booleans. We will have TRUE values on the cells that are tied for the longest string in A:A.

When we multiply a boolean array by a numeric array, we treat the boolean array as 1s and 0s. So, when we multiply our boolean array by the ROW(A:A) array, we're left with an array of 0s (where the string isn't the longest) and row numbers (where the string is the longest).

We want a single value from that, so MAX() will get us the row number of the bottom-most longest string.

Finally, we feed that row number into INDEX(array, row#), and get our result.

It's really hard for me to understand how people can write excel formulas while simultaneously being afraid of programming.

The excel shits harder.

You would rarely see someone use that formula in the wild. What you would most likely see is a hidden helper column to get the length of each cell and take the max of that.

Excel is as easy or hard as you make it.

Excel is as easy or hard as you make it.

Ah. So like programming then.

Except programmers are lazy bastards.

Accountants are another beast altogether.

Behold my nested If statements!

Years ago I worked under a guy who managed the entire company on spreadsheets. Things that could have been a simple VLOOKUP were instead done using hundreds of nested IFs. He was a total asshole so I never told him about vlookup.

Don't worry... He wouldn't have changed his ways. They never do.

Just replace the file with vlookup instead, on the last day of employment.

It's not so much that he wouldn't have changed his ways as after a while they don't know how to change their ways.

So dumb question time. The fuck is a VLOOKUP?

It's a function that returns a value from a column by looking up a value from another column.

This explains it better than I could myself

Its sibling hlookup does the same but with rows.

When someone says “BEHOLD” I always think of the mad scientist guy in the opening of Aqua Teen Hunger Force shouting BEHOLD and opening his lab coat and dropping his shorts.

Edit: Dr. Weird, #1 in da hood

This time! Will be different!

It's if statements all the way down, baby!

instinctive cough unwritten murky chop erect light brave cagey mourn -- mass edited with https://redact.dev/

If there are 1000 people making those clicks, and they all save a few seconds, you have made the world a kinder place

All at the cost of one test automation engineers soul. This is fair.

I think most us back those together in autohotkey or something and just mostly keep it to ourselves. To much of a hassle to promote

Also it's like...my special secret that lets me finish work mysteriously early. If we're good friends, maybe I'll hook you up but...

In my interviews, I’ve been asked how I would rate my proficiency in Excel. I always reply with “well I thought I was pretty advanced until I saw my analyst friend use Excel. So now I’m expert level.”

I prefer to rate my proficiency in short prose, but I can put it in a spreadsheet if I have to.

I ask this question when I interview and ask people to pick a number. Most people say 7 or 8. Then I ask about the most advanced thing they can do in Excel. The number of people that say “spreadsheets” or “formulas”is incredible.

[removed]

So when I answer “expert”, the follow up question is always “can you do pivot tables & VLOOKUP?”

Seems if you can do those things, most places are happy. (Dependent on the position you’re applying to, of course.)

But I also add “I can do some macros, too” which normally gets me a “oh that’s great but no we don’t use a lot of that here”.

Yes. That’s good.

I am neither, for better and for worse.
I nest my VLOOKUPs in my IFs based on multiple data validations and other IFs.... I went to school for art, how did I get here.....

From tortured soul to torturer of souls

Accountants are another beast altogether.

Accountants aren't the only users of excel.

Source: civil engineer

Indeed!

Source: audio engineer

civil engineer is a contradiction in terms LOL

How do you figure that?

Accountant here

ARRRRRGH WHY THE FUCK WON'T IT STOP PUTTING MY LONG ASS NUMBERS INTO SCIENTIFIC NOTATION WHEN I OPEN MY .CSV FILES TO EDIT SOMETHING???!!!

ive been working on some political bs and one of the few absolutely essential roles that must be filled (notably by someone else since i cant do it) is "accountant".

and not just to track data. because these weird bastards seem to like working with numbers and making them add up correctly, and that is essential to the health of any program of any sort.

Excel is used so broadly beyond Accounting, I think many people would be amazed with the amount of extremely important corporate data that is created and stored using Excel. Example, Consumer Product companies use Excel for nearly all sales, planning, allocation, finance, and even marketing data. Excel will outlive Microsoft mark my words

Helper columns for prototyping, final version condenses it all into single line formulas so when people try to steal my workbooks they nope out

I have never felt so seen in my excel coding methods

late to the thread but yep, nesting formulas in one cell is the anti-edit-by-coworkers once they learn that legacy sheets are not locked by default..

works every time lmfao, some even started on taking excel seriously as a hard skill so that's nice

This is me doing any complex string manipulation in SQL.

You might find AI2sql helpful; it’s an easy-to-use tool to help you generate SQL.:) http://app.ai2sql.io

I honestly don't think I'd find writing an AI parseable sentence for this kind of stuff any easier than writing the code in the first place haha!

I guess that disqualifies your use of LET.

Lol I use a very similar formula in my work

=index(x:x,small(if(y=z,row(x:x)-row(x)+1), column(x1)))

Returns values horizontally

Nice. There's plenty of ways to do the same thing. I always try to make it as simple as possible and never commit formulas like that to memory.

I’d love suggestions tbh, it’s dumb the purpose I’m using this for but if you can save ram capacity it would make life easier

Wait, you can make helper columns hidden? that's a great idea, I'm going to do that right now on a file I made for work.

You could even pull them into a different sheet.

Or you can write your own functions and embed a script into the document.

Hidden columns works for what I'm doing, all the hidden functions are just simple w*x=y intermediate results to do x/y=z results (we only care about z)

Any self respecting writer would consolidate/nest that helper column into the formula though, no?

I'm not sure what you mean exactly but i would much rather have a hidden helper column when someone else is trying to figure out what got messed up with the formulas in a year. It's fun at first to figure out how to wrap everything into one big formula, but it's more fun to not want to throw it away and start over when you need to fix something later.

Yea I almost always go for helper columns instead adding on to formula complexity. There is very little “cost” to just adding a column to make answering the question easier in steps, and allowing others to follow your work if their excel knowledge is weaker

If it’s just for my own use? Then sure, I’ll throw on my wizard hat and do some wild shit just to see what I can do, but most of my work has exponentially more impact if it’s both useful and shareable to the masses 😁

You'd think so if you didn't have loads of experience of debugging those combined formulas. Novice programmers make the same mistake, specially if they're from a mathematics background - they want information density and things to look tidy. I think people have an inner drive to build something clever and impressive.

Expert programmers try to work against this compulsion and write code that describes how and why it's done, so that future readers can easily understand and are impressed with its simplicity. Once you're good at logic that's the next step: choosing good verbs, nouns and adjectives and putting them together in a way that tells a good story. It's what makes software more about English than mathematics.

Nah, helper columns are really great for checks along the way.

Depends the use case, quick and easy way is to do what I mentioned. If it's going to live in some report, then yes, a formula would make more sense.

But Excel people are not "writers", unless you are talking specifically about vba. More modelers/problem solvers

a hidden helper column to get the length of each cell

So kind of like extracting a function

Why though? It's the same run time of N

Or is it just 'harder' to remember one way

Stackoverflow section on excel and VBA is just easier to use and more forgiving

While condensed, results are instant, debug errors are descriptive (I rarely experience that outside of Autohotkey and sometimes Python and Excel tells me when I forgot a parenthesis), and the lines are somewhat short and autofilled. In regards to macros, I concede.

Ditto for Excel people who don't take the extra, arguably easier step, of learning SQL. You can write queries that so work in other environments and they are infinitely easier than the standard formulas, IMO... plus there are query type structures even on clones like Google Sheets.

Excel isn't really difficult, it is just obtuse for what people try and use it for. It isn't a CRM. You can, I have seen, make millions and millions and millions of dollars using nothing but Excel on your back-end (essentially), so the point I am making is moot, just that those same Excel wizards would have probably hit billions with a bit of SQL :).

cries in As400 greenscreen

Hey, don’t knock the AS400. You can create some pretty awesome queries using the WRKQRY in the command line. =)

It's so hard getting people to learn PowerQuery, where you can at least do SQL style joins, and it feeds into Excel or PowerBI. You have a nice UI to even do ETL without much need to learn programming.

It's so hard getting people to learn PowerQuery, where you can at least do SQL style joins, and it feeds into Excel or PowerBI. You have a nice UI to even do ETL without much need to learn programming.

Hey, I totally hear you! Also AI2sql is a great way to do some pretty advanced stuff right in SQL. It's so easy, too—you can just write your questions and click a button to generate SQL . http://app.ai2sql.io

You might find AI2sql helpful; it’s an easy-to-use tool to help you generate SQL.:) http://app.ai2sql.io

I played around with this and some other AI programming tools (been a software developer most of my life), and I think they are great for basic use, definitely.

I am currently looking for an AI that can take any set of data (like a database of tables) and use machine learning to guess about relationships between tables and data up various metrics based on the data it finds.

Could be sales data from a mechanic shop, or widgets a factory produced last quarter.

Looked in on it a bit on my own to see if I could develop something like that, and I think I would have better luck building a script that just tries to guess a lot of things and procedurally generate content in a similar manner, but I feel like an AI that can do this is maybe out there now or right around the corner.

Serious answer:

  1. Excel is already installed and being used.

  2. Programming takes a smart nerd to do.

  3. Programs are long and complicated. An additional excel formula is only n + 1 harder than your existing spreadsheet.

  4. Much easier to do a quick and dirty fix in excel that you know rather than learn how to program.

It's also really hard to convince the 60+ year olds who barely even understand excel to use a program they don't understand (literally any executable from actual programming). So writing janky ass excel formulas, or incorporating one-button-click macros is about the closest you'll ever get.

[removed]

Isn't that programming language called Visual Basic for Applications?

Yeah... It's not exactly python because... it predates it and is natively supported in Windows? Visual BASIC gets a lot of flack but it's a Turing complete language and for a hot minute (C# kind of killed it) there was a 50/50 shot that any given Windows app you used was written in it.

VBA is basically the same thing w/o the RAD GUI part.

Me is that guy who's the most proficient in Excel and Google Sheets in the company but soooo afraid of coding. Basic HTML or tracking code is fine. But anything pass data layer is a wilderness for me

I'm the complete opposite. I love learning new programming concepts and applying them, but I can't stand working in Excel. And I totally admit that it's probably because I haven't spent the time to actually figure out a good workflow.

Python and SQLite is far more powerful than excel

Is that real? I love writing out formulas and getting crazy in excel… deathly afraid of programming and worried I’ll get lost with the different C+, C++, Java, etc (I don’t even know if that’s right)

1000%.

Literally just try python. I personally don't like using python, but for reasons you'll probably never care about.

Excel is just programming for business people. Shit gets actually way more complicated than just writing a script.

I remember a talk where a guy said Excel is the most popular functional programming language.

It is technically true. Excel has no side effects, any cell can only reference any other sell, etc.

If you are good at excel, start with recording some macros and studying that vba code until you know what they are doing. There's lots of free tutorials for vba programming as well as python.

Just learn Python

I’ll give it a try!

Just so you know, Python was specifically built to be easy and simple.

It took me maybe 2 weeks of learning to be able to use it on my job.

What kind of things do you use it for at work? I played around with it years back on Codecademy but never really figured out how to transfer it to something useful in the real world. I'm reasonably proficient with Excel, PowerQuery, VBA and SQL, but they're kind of already part of systems I'm using so a bit easier to figure out.

Maybe this book is useful to you? The web version is free: https://automatetheboringstuff.com/

Thanks! I'll give it a read.

I'll give you the example in R (similar if you're use python).

Find which string in column A is longest

which.max(nchar(df$A))

This is base R. You just install the software and it works. It is backward compatible so it works on R 1.0 from decades ago.

explanation:

df is the name of the spreadsheet. $ grabs the column called A. Then "nchar" count the number of strings. R by default works on a column or row, so nchar(df$A) will give you a count of length of strings for the whole column. Once you have that, which.max() returns which element is the maximum.

Tell me that shit is harder than whatever the crap is EXCEL doing.

Also, with scripting language you keep a copy of what you have done. Have you ever written down what you have done with this dataset or that one? Well with programming you don't need to because it's all in the code, and you don't have to click on the cell to read it.

Last, we don't code in c/c++/java for data processing. You are fine with something much easier like python or R unless you are doing algorithm development. And let's face it. You are using excel. You are doing data science.

Not so hard, It something like regexp for me.

Don't take the name of that stuff in front of me, you just put me into an anxiety attack

Well that's how we end up doing macros but real programmers bully you if you call that programming

I'm not afraid. It's just that I can cobble this together in an hour doing incremental stages and getting instant feedback if I write something wrong.

Meanwhile, programming requires me to spend at least a few days to get to know everything and figure out how to do things I do in excel already. That's simply not feasible since I get an hour or two per week to sit down in front of a computer at work and I'm not going to learn programming in my unpaid free time.

You get instant feedback in programming, especially if we're talking about something like "data in/data out".

The counterpoint to "I'd have to spend time" is of course that by avoiding learning the better tool you are actually costing yourself more time in the long run.

It's like saying you're going to jog to work because learning to ride a bike takes time and effort. That's true... at first. Then you save a significant amount of time after you've developed the basic skill.

I'm not going to learn programming in my unpaid free time.

If this skill is for work why wouldn't you develop it at work? What if you spent 10-20 minutes a day just learning some basics? At the end of the year that would be a 40+ hours of time you spent on learning. Sure, it would be slow... But slow and steady is still progress.

you are actually costing yourself more time in the long run.

That's the company's time. I'm pushing myself every day and if I got more efficient, they'd surely find more things to pile on to my already overfilling plate.

What if you spent 10-20 minutes a day just learning some basics?

I am way past basics. That's the problem. Another problem is 10-20 minutes a day is not something I have. I'm squeezing 9 hours of work in 8 hours daily and if I managed to do it better, I'd be tasked to squeeze 10 hours instead. It's not really worth the stress for minimum wage.

Well, that is me.

Exactly, this would be so much easier in Python

Nobody is interested in python scripts in the workplace. They want an excel sheet they can operate.

Productivity >>>> fancy script which achieves the same thing.

Excel is algebra with words.

programming is Ad-Libbing with fractals.

I've been slogging through a colleague's VBA code. There are definitely things that could be replaced by library calls in a modern language..

I think it's an awareness thing. I've been using advanced excel shits to manage our data in production foe the longest time and I just recently learned how Python basics are just the same formulas I use in excel. Now I'm into studying data engineering because of it

This is me. I will write paragraphs in excel and I’m just now getting to python. It’s shockingly similar, but for a reason I can’t define, excel makes more sense. Something about it just clicks and I can’t put my finger on it.

It's all a frame of reference. Functionally data manipulation is data manipulation.

The level of depth you can get to with a general programming language is significantly deeper* with less complexity to get to that point

*Excel is turing complete, technically you could compute any problem with it, but like cmon.

I know it's more of a personal preference, but I'd prefer to use an XLOOKUP

=XLOOKUP(MAX(LEN(A:A)),LEN(A:A), A:A)

Thats why I python now

https://www.geeksforgeeks.org/reading-and-writing-xml-files-in-python/

Reading and Writing XML Files in Python

Difficulty Level : Hard

and, I'm out.

I find R to be an easier language to transition to from Excel.

I don't know why I can't figure this formula out. I need the opposite of this website. One that breaks down formulas.

Holy fuck, that's a smart formula. Also goes to show how complicated Excel can be - in C#, you'd just write array.MaxBy(s=>s.Length), and similarly in many programming languages

Can you explain this for me, please? I do a lot of Excel formulae and I don't think I've seen an equals sign used inside a formula like that so I'm not sure what it's doing

Sure!

=INDEX(A:A, MAX( ROW(A:A) * (LEN(A:A) = MAX(LEN(A:A))) ) )

The calculation happens from the inside out.

First, expand all the A:A as arrays of numbers that these functions act on.

LEN() acts on a single cell, so it acts on each cell individually. LEN(A:A) creates an array of numbers, with a value in each cell equal to the length of the string in the corresponding cell in A:A.

ROW() also acts on a single cell, so it creates an array of numbers with values equal to their row number (ie, 1 2 3 4 5 6 etc).

MAX() acts on an array, so when given LEN(A:A), it creates a single value, the length of the longest string in the array.

So when we have:

LEN(A:A) = MAX(LEN(A:A))

We compare an array (on the left) to a single value (on the right), which creates an array of booleans. We will have TRUE values on the cells that are tied for the longest string in A:A.

When we multiply a boolean array by a numeric array, we treat the boolean array as 1s and 0s. So, when we multiply our boolean array by the ROW(A:A) array, we're left with an array of 0s (where the string isn't the longest) and row numbers (where the string is the longest).

We want a single value from that, so MAX() will get us the row number of the bottom-most longest string.

Finally, we feed that row number into INDEX(array, row#), and get our result.

Cheers!

That's fantastic, thanks. I've never thought to include arrays in my formulas. I'd always expand them out physically, generating the actual length numbers for each row in another column, then taking the max of that column as the row parameter for an index formula. This is going to change some things, although I'll still probably "build out" more so my mistakes jump out at me haha

Arrays are great, I would recommend getting familiar with sumproduct to avoid CSE arrays as well as experimenting with boolean tricks when you need something with a little more flexibility than Index/Match.

Natural language processing seems to need some more work here (but it's good!)

I asked "match columns a, b, and c to d, e, and f" and got a good formula. However. Adding "match columns a, b, and c to d, e, and f, and add one day to the date in column d" did not seem to work.

So basically it's Google?

This is very cool. I submitted a hard problem it wasn't able to solve - required doing a match where the lookup array was two references (each created using an indirect on a range compared to a value) are multiplied together (basically, creating a list of 0's and 1's to find where two criteria are both true).

But it did come up with an index match formula that was close to performing one of the search criteria properly, so that was pretty cool.

EDIT: If you're finding this tool helpful, there's a donation link on the website. The author is open about the costs to run this site (visible near the top of the page) and those costs are adding up. Consider chipping in a couple bucks.

It’s definitely not perfect. I’m collecting data now to hopefully improve the model’s output.

I figured that was the case. Good luck! Impressive already.

Not sure if you made it or not — but it’s wicked cool! I bet Microsoft would be interested in such technology.

I did. Thank you so much.

Super cool! May I suggest you make some copy about google sheets support for seo?

Im working on building something similar for all code bases and will definitely incorporate google sheets. Great reco.

SEO is my next focus. I’m thinking about leveraging the prompts and model output to show show a table for top questions (ie: a SUMIF page), where it would show all of the entires for that formula so someone can find one that best suits their need.

Hopefully you won't sell it to microsoft for it to become a paid service for them. Seen so many small and medium projects get swallowed by corporations like it only for them to then neglect or ruin it.

Hey man - people gotta make money!

He can make it a subscription based service when it's "ready" and people will buy it. That won't leave it in limbo with microsoft's 200 pet projects with no guarantee of seeing daylight again. He can definitely monetize it.

If you can do it programmatically make a landing page for as many results you can so people land on them googling “find all results in google sheets tab” etc. should be able to write it once and just have it make dynamic pages.

Yep for sure! I need to clean it up and do some fine tuning to the model. I don’t want to show poor output results. Thanks!

Yes integrate that with speech to text. Then you have a modern office assistant 📎 who listens to your command and writes the formula.

Return of the 📎

Damn, you made this?

Well done. Awesome follow up too!

[deleted]

You can do something like:

=MATCH(1, (INDIRECT(some_range_here)=4)*(INDIRECT(another_range_here)<>12))

This would find where the value is 4 in the first range, and not 12 in the second range, the ranges need to be the same size and not offset relative to each other (row 1 in range 1 has to be paired with row 1 in range 2).

There's a more detailed description in my post (not comment) history, look for the excel question posted to r/excel (I only made 1 post there).

What a bunch of nerds! I love it!

Person 1: the glass is half full Person 2: the glass is half empty Excel: the glass is January 1, 1900

I actually just went though a similar thing as you recently. The FILTER and SEARCH functions are your friend, as well as the ability to load MATCH with a TRUE value and with multiplied criteria (e.g. MATCH(1, SEARCH("Hello", A1:A2)*SEARCH("World",B1:B2),0)).

Yeah, filter wouldn't have worked in my problem, filter created a filtered list and a match gives you the result relative to the filtered list.

I needed the row # from the original range.

Ahh, I did read your description wrong then. Yeah filter wouldn't return the row number, ~~and unfortunately Excel doesn't have a ROW() function like COLUMN() to give you what you need, afaik~~ but Excel does have a ROW() function that'll do what you want.

I've used filter recently to grab cells based on a reverse substring search on a range of values, something akin to SEARCH(A1:A2, "Hello") as opposed to the normal way where the arguments are reversed, and filter + some iserrors helped me return the right cells that I'd average later.

Still, having an AI that would have saved me like 3-4 days figuring that out would have been pleasant

For sure, this is a great tool.

Fyi match (and xmatch in 365+) will return a relative row number, so if you feed it column d rows 2, 3, and 4 and the result was in row 3, it would return 2.

Very useful for getting row numbers. I like to use this to build references to cells using indirect.

Xmatch has the (huge) advantage of letting you do a reverse search (start with the last row and work backwards) since both will stop when they find a match.

Of course, that would be a makeshift way to grab the row as long as you start from 1:1. Any other time, say if you have a title block that takes up like A1:D:4, then you'd need to just have an offset, which is really trivial.

Wonder if the AI got that one lol

The AI did do an index match, not sure if it would pick up on adding an offset though.

Might not be understanding you correctly, but Excel does have ROW. I use it a lot as my quick and dirty numbering when a table is offset somewhere, just a quick =ROW(A1)

Seriously!? Oh the time I would have saved... Gah, I'm sorry. Hate spreading misinformation. I'll fix my comment

Hey happy I could help your future projects!

I would have just gone straight for an index formula so it's pretty clever in that it can read your intents even when the request is very slightly ambiguous.

Maybe you weren't specific enough and you're blaming a poor bot :/

It's kinda funny, i just saw a solution to this on tiktok that involved converting the arrays to tables then power pivot tab -> add to data models -> diagram view, then link the tables by the common key

Index match formulas are my favourite ❤️

I’m a behavior analyst and often have to analyze behavioral data along multiple factors. You just saved me SO MUCH time that I can now use to help my special needs kiddos directly. Sharing this with everyone I know. From the bottom of my heart, thank you, you beautiful human.

Wow, this comment alone makes it all worth it.

The site blew up today. Couple hundred dollars worth in API costs. I haven’t received any donations in hopes of keeping this project going and started feeling discouraged because I’m not in a position to pay for that amount out of pocket every day, but this comment alone makes it all worth it.

I know today is an anomaly and it won’t always cost this much, but I do hope I can continue to run this project to help those that need the help - without charging. If it does get too pricey, I can explore charging a $1-2 monthly subscription.

We’ll see in another couples weeks, but for now - I thank you. So so much.

And feel free to DM me or message me on Twitter if there’s anything else I can help with for your kiddos.

If there’s something else that I can build that will help them, I’d love to build it.

From someone else, thank you!! I gave some money already but if theres a way to support you on an ongoing basis, please let us know!

That’s amazing! You’re the first. Thanks so much!

I need to figure out logistics on the pricing. I’d love for this to be a free product, but need to find a way to break even on the cost. I may explore paid ads or a $1/mo subscription! But your donation goes a long way. I’ll keep this running and paying for it as long as my wife doesn’t yell at me ;)

I've been learning all about formulas lately in my new job, and this is going to be a lifesaver for a newbie like me. I hope you get more and more donations, but really, I hope Microsoft puts a gigantic donation in your hands for this.

What are your expenses going to? The web server should be like $5/mo. Is the AI engine super processor intensive?

OpenAI API requests.

The web application I use only cost $40/mo and the hosting and domain is minimal. It’s just the API costs that quickly add up.

You could make some previous lookups searchable so people don't need to use a new query. Maybe verify those answers.

I don't know how much each search costs you, but if it is good enough, you could work with a package for x-amount of searches. And perhaps throw a free search in every week or so.

Or like the free mobile games, look at an advertisement and get a free search

For sure. Thanks for the comment.

If you're penny pinching, you should be able to run the actual web server on a $5/mo Droplet at Digital Ocean (or equivalent elsewhere). This obviously depends on your ability and willingness to use a VPS and set up Apache/NGINX/Whatever.

The OpenAI I can't help you with. :) Like /u/SmokingCrop- said, using a cache in front of that API could help. I pay for a service to match street addresses and everything I send to them gets normalized then the response cached. That means trimming extraneous spaces, lowercasing the entire string, etc. Then I cache the response so I don't have to send that API call again. Small things but they make a difference! Good luck and cool project!

Did you tried to ask for free monthly quota for your site? Open ai is generous and may provide you with a free quota if you apply.

I went through the $18 free trial. I can explore that. Thanks so much!!!

This is the form to apply https://share.hsforms.com/1bFO2-ad4QHyLJlBPfNR8dw4sk30 .

If you need more tokens, you can always request a quota increase. When you’re ready to go live, you’ll submit a Pre-launch Review Request which will also cover any additional quota increase requests

Awesome, thanks so much! I actually requested a quota increase mid day yesterday. I already had a separate API key from a different account ready to go if I didn’t get the increase in time. I’ll need to increase it again. Thanks again.

By the way, can you let me know what data are you collecting? Like are you able to see what promts the users of the site are making?

Both input and output. Used to fine tune the model since there’s some formulas specifically that it struggles with.

Hey, I saw your notes about cost and saw the new header on the website.

I added a note about donations to my top post. It looks like you've made it to break even. I hope the note helped!

I have! Thank you so so much! Obviously wasn’t expecting for this post to go viral. Was really just looking for some testers to start gathering data.

I’m not going to charge as long as I can break-even. If needed, will run some ads. We’ll see.

Really appreciate it!

No problem, I originally saw a cost over 1K but it looks like that was either an error or you were able to get some credits. Made me sick to my stomach thinking someone was out that much just trying to be helpful.

Glad your project is finding such success.

That was me publishing changes to the site after a long stressful (and awesome) day, where I set up something wrong on the backend API side.

You're in the market for a Patreon at least

You might look at how https://nightcafe.studio/ handles credits for rendering. Everything is credit based and you can only get a limited number of free credits per day or you can pay for more credits.

This is a great suggestion for keeping it free while incentivizing donations.

I'm at work and GM blocked the site as a security risk. 🤷‍♂️

How/where is it hosted, depending on volumes you’re seeing you should be able to get a fair way running it on a cloud providers (AWS, azure) etc free tier

Whatever magic happening in the backend could be a lambda that scales to 0 so you only pay for the time it actually runs etc.

Nice, now do RegEx. I’ll pay for that.

In the works, actually. Feel free to follow me on Twitter for updates.

Omg I would pay for something where I can enter a string and highlight the bit I what to find and it gives me the regex

I use this quite a lot https://regex-generator.olafneumann.org/

That's exactly what I want. Thanks

There would be too many ways to find a single example, it would however be cool to provide multiple examples and have it give an output

Easy, just have it only output the best way /s

Same people just want to watch the world burn.

You, buddy, are attempting to start the fire that does it.

An identical string in multiple places can be matched by multiple conflicting regexes

[deleted]

That's dope!

I tried something simple: The word "doors" followed by a space, then "main" or "bedroom", a space, and then "on" or "off"

It worked perfectly: /doors (main|bedroom) (on|off)/

Then went with something more complex: The word "coords" followed 2 positive or negative numbers separated by a comma. Ignore spaces

It worked OK: coords[+-]?\d+,[+-]?\d+

It didn't do the "ignore spaces" on this one but if I remove the negative number requirement, it did the spacing correctly: coords\s\d+\s,\s*\d+

Honestly, I'm impressed.

In the meantime, there's some really nice regex calculators out there. The tricky part can be finding one in the language you need (I'm looking at you Javascript), but the good ones have a library where you can look up regex statements, write regex, write text, and see what it matches.

The nice part is, you can leave your text in while you change the regex to see what it captures on the fly. Then when it gets what you want, write similar text that might trip it up or you need to be careful about to see what it does.

I need the regex to English version. Writing them is easy, but figuring out what's going on the next day is bloody impossible

Regex101.com

Not bad, will bookmark this for next time, cheers!

Or SQL.

I hate joining tables and getting unexpected results.

You can practice with AI to learn SQL Joins 😉 http://app.ai2sql.io

SELECT *
FROM ufn_DatesBetween(,) ua
FULL JOIN ufn_DatesBetween(,) ub
ON ua.Date = ua.Date

(364.2422×9999)! rows affected

There was one posted on Reddit recently. Don't recall the URL, but it should be easy enough to find it.

Nice, let's see Paul Allen's RegEx.

this is awesome weird one though :

if a1 is 1 AND b1 is either aaa or bbb or ccc then yes if not then no

this gave the correct answer of

=IF(AND(A1=1,OR(B1="AAA",B1="BBB",B1="CCC")),"Yes","No")

but

if a1 is 1 and b1 is either aaa or bbb or ccc then yes if not then no

gives the incorrect one of

=IF(OR(A1=1,B1="aaa",B1="bbb",B1="ccc"),"yes","no")

weird that just capitilising the and makes all the difference

Probably a sort of PEDMAS, the caps makes it two different terms while no caps make it one term.

Hey guys, it works.

https://i.imgur.com/pfRxgB5.jpg

This made me chuckle lol

[deleted]

Microsoft definitely wants this kind of thing built into windows. That's why they got exclusive commercial rights to openai gpt3 and I think codex. Just full on tell your computer in natural language to do anything you want.

Like I was helping an idiot with his infinitely nested music collection that was pretty much scattered and randomized when I could have just said "Collect all music files on the computer, delete duplicates, sort them into albums ordered by track number, sort albums by artist ordered by year, order artists alphabetically, put into music folder." And it would just do that.

Also now that they own Bethesda, maybe they can have full ai npcs in the next elder scrolls. Just full in conversation nearly indistinguishable from humans. Just a few years away from that kinda stuff.

Exactly.

I consider myself as a pretty advanced Excel user, but I feel it's pretty tedious with making formulas.

If there was a built in function that would write it for you would be amazing.

Hell, I'd love even just a troubleshooting tool that you could tell what you are trying to do.

Hell, I'd love even just a troubleshooting tool that you could tell what you are trying to do.

I'm like 90% sure there is, was originally confused on why op made this but it seems that everyone's loving it

Found the British person.

Nope, but from a Commonwealth country.

It's a Simpsons reference

The quote is from the Simpsons. "Microsoft are" is British (or Australian or New Zealish or Canadian or Virgin Islandian).

Really?

I didn't know that was a thing.

Is it is vs are for corporations or other cases?

In general, in the US we treat companies and collectives as one. So like "Nintendo is" or "Congress is".

We make exceptions for like band names or teams if it's plural. For example "The Rolling Stones are visiting" vs "Led Zeppelin is playing".

What made you think that? I'm reading it and can't work it out

Microsoft are

vs

"Microsoft is"

Hmm. I don't know this. Both sound correct to me.

Is this because Americans view corporations as single entities, like how they're classified as legal persons in court cases or whatever in USA, while British view corporations as large complex things of many parts?

Am I overthinking this? Why the difference?

That's exactly it. We see the company as like a monolithic monster that is doing something. It's like the difference between "My English-literature class is the worst one I've ever had" vs "My English-literature students are the worst ones I've ever had" ("my class is" vs "my students are").

Well, I say "it's exactly it", but I meant just the part where we see it as a monolith, not because we see it as people. We also call our parliament (Congress) a single being.

Both would be seen correct in British English as far as I know. Perhaps that's just due to loose grammar and Americanisation though

Microsoft is already making money from this since it uses their AI

Thought I was being clever, I'll admit that I got outsmarted by AI. Looking at the other comments, it seems I wasn't as clever as I thought.

put the word pickle in column b when column a is an even number

gave me

=IF(A2%2=0,"pickle",)

neat...

This... THIS

I have no words for this. I'm self-employed and carry my accounting in a spreadsheet and I was afraid some conditional operations weren't possible in spreadsheets. This is beautiful!

I've just come across one or two "incomplete" answers, but it might be that the original request has a flawed logic and it might work if reworded. Gonna test some more.

In case it helps, this didn't work:

Input: Count how many distinct values are in a column

Output: =COUNTIF(A:A,A1)

I mean, not QUITE. This just counts the occurrences of the value located in A1.

I'm looking for a way to return the various unique values in a range (and later count them).

If you want to return the various unique values in column A:

=UNIQUE(A:A)

​

If you want to return the number of unique values in column A:

=COUNT(UNIQUE(A:A))

​

If you placed =UNIQUE(A:A) in cell B1, then type this formula into C1 and extend it using the drag on the bottom right of cell C1 (when cursor changes to a plus sign) down to match how many rows you have in column B:

=COUNTIF(A:A,B1)

The above will produce a side by side with column B showing the unique value and column C indicating how many times these unique values occurred.

Note: In Excel, if your column A contains empty values, =UNIQUE(A:A) will display a 0 even if your dataset doesn't include 0. This 0 doesn't get counted in =COUNT(UNIQUE(A:A)). In Google Sheets, =UNIQUE(A:A) will not show a 0 for empty data in the column.

Interesting, I didn't know the UNIQUE() function. Curiously, libreoffice doesn't have it or anything like it, I had to test it in a Google spreadsheet.

I find it even more curious that it automatically fills cells under it without extending the formula.

Thanks!

Not all softwares provide the same functions (such as Microsoft Excel has XLOOKUP which is a new alternative to VLOOKUP and the INDEX/MATCH functions which isn't available on Google sheets).

​

For a list of various functions in Google Sheets, can browse through the list HERE to see available options.

The above function example to view uniques and count can also be done without using any functions at all using pivot table (really helpful to use to analyze data).

Highlight the data in column A (include a title for the first selected cell) that you want to analyze, go to insert tab on top, and insert pivot table.

After in the Rows option, add and select the column title that you set.

In the Values option, add and select the column title as well. Change the "Summarize by" option from SUM to COUNT and you will see the unique values and the count there.

What version of excel does this work with? I don't see unique in mine.

According to this site from Microsoft, it lists the versions that support it as: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2021, Excel 2021 for Mac, Excel for iPad, Excel for iPhone, Excel for Android tablets, and Excel for Android phones

https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

Though there is a note in the article that says "This function is currently available only to Microsoft 365 subscribers." so I'm not sure if those other apps such as Excel 2021 have it included or require you to be signed into an account that has Microsoft 365.

Google Sheets has it available for free if you need to use that function.

Edit: I just tested it out on Excel for Android using a Microsoft account that doesn't have Microsoft 365 and it has the Unique function included so all those apps above probably have it.

That explains it my work uses Excel 2016. Thank you!

it works! (I'm not exactly sure how/why it added 'wee all the way home' to it's response but i am 100% not upset!

"if d2 is an even number then say woopty doopty, if it is an odd number then say woo pig chewy, if it is a multiple of three say wee wee"

This will just take a few seconds.

wee all the way home

=IF(MOD(D2,2)=0,"Woopty Doopty",IF(MOD(D2,3)=0,"Wee Wee Wee All The Way Home","Woo Pig Chewy"))

edit: i just realized, it also added it to the formula, along with the original request. wtf that's hilarious.

"Proficient in Microsoft Excel"

Internet connection and ability to ask a question will eventually be the only prerequisites.

Already is.

Helps to know what to do with the answer, though. Or know what to ask to begin with.

Cisco Umbrella Security blocks that site saying it identified a security threat FYI.

NextDNS does too, because it’s a newly registered domain (NRD).

Honest question: Is there any reason to include VLOOKUP answers over INDEX/MATCH? Most experienced Excel users seem to prefer the latter, and I think they even implemented XLOOKUP in more recent versions to replicate its functionality. OTOH, it's been a long time since I've used VLOOKUP, and the only thing I remember about it is that it was wonky and prone to breaking/giving false positives.

[deleted]

I used to use vlookups a lot for work and recently started switching to xlookup and it is great! No more having to make columns in a specific order to get the formula to work simplifies things so much. I often have to work with two data sets from two different teams with a unique identifier between them (but in a wildly different column order each time but that's another battle) so this saves me a fair amount of time and effort!

It can correctly predict the next powerball lottery results.

Excel formula generator https://imgur.com/a/cy2815R

Hahaha,

Calculate the square root of Tuesday three weeks from Easter...

=SQRT(TUESDAY(EASTER(YEAR(TODAY()))+21))

Thanks for this web app! A friend just showed it to me and I tested it out. I rarely use Excel for my analyses and the problems I asked the bot might be different from what you trained it with/it is intended for. Of my 3 queries so far, two of the results that came out were with functions that I couldn't find in my Excel installation and that are also not listed by Microsoft (I'm using Excel for Mac and it's a German localised installation).

Here go the questions:

Q: Calculate the prediction interval for the linear regression of column A as independent variable and column B as dependent variable

A: =PREDICT(A,B,1)

----

Q: (Can't check the exact wording of the question anymore but I wanted the bot to create an ARIMA model for a timeseries in column A)

A: ARIMA(1,0,0) (or similar)

Jesus H what does any of this mean? I thought I was pretty pro with excel, but now, after reading that, it feels like I'm just a smoothed brained crayon eater.

I feel like that most of the time too, I think it’s a universal thing..

I mostly use R or Python with Pandas/Numpi for my analyses, thus I’m not even sure what is possible with Excel and what not.

As for your question:

When you create a linear model (“trendline” in Excel), e.g you could predict the weight of a person by knowing their hight. If you have enough samples collected (measuring out people), you can say with a certain confidence, that other people with a certain height will have a corresponding weight. This range is called prediction interval.

As for ARIMA: It’s a model for timeseries forecasting, quite popular in economics but also many other fields. An example where you would use something like this: Imagine Passengers of an Airline over time. There are most likely trends, seasonal/cyclic effects and some other effects that are “random”. With something like ARIMA it’s possible to make predictions/forecasts how the Passenger numbers will develop.

All of human civilization is going to come to an end when the wrong person uses something like this and assumes the AI provided them the right answer because they can't understand what it returned

That is a very valid concern and I think it is something not talked about enough. Understanding the why behind something is so important.. Just look to people using google as a Doctor for example.

Also if something goes wrong with the code it gave you, how do you fix it? Ask the AI to fix it I guess...

Can't find the article, but I remember something about scientists using an AI to decode a cellular mechanism, and they ended up with an answer which had so many variables and interactions that it wasn't comprehensible on a human scale.

Which raises an interesting question about our study of reality: it may be (is) so complex that we simply cannot comprehend it, and are instead using oversimplified models which are sized for our own limited minds. To truly study the natural world, we're going to have to surrender our investigation to increasingly advanced AI, but that AI may not be able to explain the results in a way that humans can comprehend, particularly without discarding crucial details.

Will we reach a point where science is no longer a human endeavor because we've surpassed the limits of human comprehension and we simply trust the workings of computers to occasionally tell us cool things that they've discovered?

Will we make ourselves obsolete? What becomes of humanity when we're reduced to reading computer logs, trying to divine what our creations have discovered?

Finally some some very hard questions answered by excel! My life is saved

Meaning of life: =42

Who shot first:

=IF(A1="Han", "Han shot first", "Greedo shot first")

Second one has faulty logic, should be:

=IF(A1=“George Lucas meddling”, “Greedo shot first”, “Han shot first”)

Q: "Where will I find true love?"

A:

"There is no definite answer to this question, as everyone's experiences with love are different. However, some people may find true love by taking the time to get to know themselves and what they want in a partner, and then actively pursuing relationships that have the potential to be fulfilling. Others may find true love through more random means, such as meeting someone by chance or through mutual friends. Ultimately, there is no surefire way to find true love, but taking the time to reflect on what you want in a partner and being open to new experiences can help increase your chances of finding lasting love."

Thank you, Skynet

The answer to life, the universe, and everything = 42.

Whoa, the answer to life, the universe, and everything is February 11th 1900! - Excel, probably

That is painfully hilarious.

but what is the question?

Bookmarked, and not that i dont love giving stuff like this out for free, but OP you could sell this license to companies… for like a lot.

Absolutely... but until then, thanks for letting us plebs use it for free.

Will there be a Google Sheets update at any point?

Yep! Feel free to follow me for updates. Probably going to use Twitter for updates.

bro i love you

I love you.

I have a lot of updates to make and plan on adding more code bases / features in the future. Feel free to follow me here or on Twitter.

I asked it if there was going to be another season of real house wives of Orange County and it responded that there is no definitive answers because the shows producers haven’t announced it yet

Will have to try this when I get home. I've been looking for a while for a way to calculate my paycheck where I can input hourly wage and hours worked, and get an answer that will automatically account for shift differential, night differential, holidays, etc.

Thanks to you, Clippy is laid off, homeless and can't afford life-saving rust protection.

Thank you so much for posting this, I have been doing my wife and my finances on Excel since we purchased our home last year, and I dread it every two weeks importing statements then changing them to house (her or my money), Savings, Car, etc. then calculating what goes were.. This has made like so much easier with =SUMIF(N3:N28,"House",M3:M28)+SUMIF(Q3:Q28,"House",P3:P28)

=SUMIF(N3:N28,"Food",M3:M28)+SUMIF(Q3:Q28,"Food",P3:P28)
Etc,
Etc

Now can we build something that will look at my imports and know if I was the one who spent the money or my wife or if it was a house expense? /s

Thank you so much for this.

How did you train your model? Or are you paying for the OpenAI API?

OpenAI, with some custom adjustments. Adding on some fine tuning now that this post blew up and I have data to play with.

Yours is the perfect use-case - bypass complexity for non-technical users.

It's insane what AI is doing, especially with coding, via CoPilot, Tabnine, etc.

Can I ask how much OpenAI has set you back?

I actually just added it to the top of the page for transparency.

Oh man, you're not even breaking even yet!

OpenAI isn't really "open" is it?!

No, it’s super closed but with great PR.

Data? So my promts are visible to you?

It does a good job, but I found that in some situations, the language necessary to return the desired formula may not be intuitive for the application’s target audience. For example, I wanted to have an equation that would return the number 1 if the text in column B “contains” the word “mat”. I also swapped “contains” for language using “within” and “is in”. Only when I used the word “found” did it return the appropriate ISNUMBER(SEARCH formula. I’m assuming it will learn over time, but in the meantime, it would be great to see some sort of user feedback where the appropriate formula could be entered by the user for the platform to train on. However, I could see that getting messy, as both the most common understanding of the user’s input and the user’s recommend output would need to be verified.

end world hunger

=IF(end world hunger=0,"No one is hungry","Some people are hungry")

hmmmmm

Ooks like an Excellent answer

I tried the URL, but my company is blocking it "Due to a security threat". I'll try it again when I get home.

[deleted]

Possibly, but then all of our software developers and I would be blocked from Google.

I consider myself to be really good at excel and this just helped me easily figure out a formula I've been too lazy to solve on my own for a while, and taught me something new about the formula it used. Took a few attempts to word it in a way that it solved it.

For example it didn't seem to know what i meant by "today", i thought it would just throw in "today()" for me. But i just gave it a cell name in my description instead.

Wow very impressive! Congrats OP!

Thank you! It was really fun building. The positive feedback received made it well worth it (and the API costs lol).

I always have to look up formulas bc I don’t use excel enough. This will be a huge help!

Thank you so much, OP!

I’m just getting back into Excel after several years; this will really help!

This is awesome. Amazing work here. Worked well for simple text comparison and/or manipulation, which is mostly what I do when I use Excel.

Of course, with me and any AI, I gotta play with it.

Q: Satan’s favorite number

A: There is no definitive answer to this question, as Satan's favorite number could be anything. However, one possible formula for Satan's favorite number could be:

=666

Q: "What is the meaning of life?"

A: "The meaning of life cannot be expressed with a simple mathematical formula."

I get surveys that i have to manually type into autocad.

I have spent way more hours than i should have trying to manually convert their jibberish into useable data. But i really suck at excel, so much i dont even know what to type into your ai. Can someone try it for me?

Here is an example text i will often get

Thence North 18 degrees 58 minutes 02 seconds west, 9514.62 feet, thence North 18 degrees 58 minutes 02 seconds west, 4.62 feet. Thence South 18 degrees 58 minutes 02 seconds east, 14.62 feet, thence, North 18 degrees 58 minutes 02 seconds west, 124.62 feet.

The numbers all change, but the general text is pretty much the same. The problem i ran into is that the numbers, especially the feet, can be 3 digit, 4, 5, etc and not always the same.

Other times they will abbreviate some words. Or put other random stuff like “past the tree” randomly in it that can be ignored.

The out put i need is “North 18 degrees 58 minutes 02 seconds west, 14.62 feet” into 14.62

This is definitely getting bookmarked

Great stuff, one of the few I actually turned off manually my ad block to support the developper.

Well, I don’t have ads yet lol but at the rate of the cost of API - I’ll need it.

Damn, at least it will be off for when you put them on next time I go!

Cool thanks

Well this just became a bookmark...

This is great, I used it to generate the simple formula we use to calculate usernames for staff:

join 'st-' and column B and '-' and the first letter of column a

gives

=CONCATENATE("st-",B1,"-",LEFT(A1,1))

Seems to work as expected, clever bit of kit I'm sure it's easy to get it to fail using more complicated input but nice idea for a website.

Edit: also off topic but it amazes me how many people don't realise a) you can use formulas in Excel and b) it is quite powerful.

I once had a user who spent the summer holidays working out how many pupils were at Level X and what that was as a % of the class and year group. So they can report back We have 78% of pupils working at expecting level.

They did this for 4 year groups worth of pupils and 3 subjects for 3 school terms worth of data. When they showed me I said give me a copy of the spreadsheet with the names removed and I'll put some formulas in. We then worked with it for a couple of years generating graphs and all sorts until their needs outgrew the spreadsheet and they purchased a better assessment tracking system.

It's not that hard and just takes a bit of time and patience to figure it out, there's plenty of resources online to help with Excel, just give it a go people :)

Requires a signup now, so... yeah :/

Not free anymore. Only allows 5 formula per 30 days

one way to make money, he says that 97% of users were using it for free while only 3% paid.

Good work mate!

Curious as to what did you use here? Built your own model or trained something existing? T5 and Bert?

Uses GPT3 with some fixed parameters.

Assisted coding is the future. An excellent work in progress project from you David. Hope you keep it up.

Although, I'm yet to see any actual AI in it ;)

[deleted]

I’ve seen that as a recurring problem - not character number in a string. Got some work to do! Thanks.

Huh. I just tried something and it failed, I hit "not helpful" and ten seconds later I got a prompt for an update and tried again and it worked. I know that it was probably something that was already flagged, but the timing was spooky. Neat!

u/dabressler Minor feedback.

For the prompt:

“Delete the last character of a string only if the last character is a letter not a number”

It spat out:

=IF(ISNUMBER(RIGHT(A1)),LEFT(A1,LEN(A1)-1),A1)

It should be:

=If(ISNUMBER(value(right(A1))),A1,left(A1,len(A1)-1))

Don’t get me wrong that’s not bad at all, but that’s also not quite perfect, it forgot that checking characters in a string for being a number needs to have a value function, and it didn’t interpret the if statement true/false outcomes the correct way round. Still. Pretty impressive! That’s a really weird test question. Also it seems to recommend vlookup not index match, which is kinda gross!

Doesn't like quotations.

Yep. That’s why I put “do not include quotes” lol.

Ha lol. Thanks! Nice tool though.

I just made some tweaks. It won’t accept double quotes but will accept single quotes now.

I updated the language, as well.

This will fix a lot of the model requests as well.

Thanks!

Interesting but limited

“What is 9 + 10?”

“19.”

This AI stoopid - every math equation equals 21!

I’m very confused now why anyone goes through the trouble of learning xcel/sheets to this degree when things like sql/bigquery are just so much more god damn powerful and scalable.

You want to explain that one in language that anyone who isn't a programmer can understand?

Make a business licensed version. Make it very expensive. Then offer an affiliate code for marketing.

Then have literally everyone on this thread recommend it to their business and we all get rich.

It's here. People have been talking about GPT-3 being able to write code for a while.

BA's gonna need to get a new job.

I asked "The sum of the squares of column A"

It gave me

=SUM(A1:A10)\^2

Although I don't immediately know how to solve it without an helper column, the answer most definitely is false.

Edit: Apparently Excel has a built-in SUMSQ function that does this.

I'm really happy to see that the AI ecosystem is growing day by day. I developed a SQL generator AI2sql http://app.ai2sql.io, and it's great to see how AI can make our lives easier, and be recognized by everyone.

If the value of the cell is negative make the background red

=IF(A1<0, TRUE, FALSE)

Mmh

If you can’t do it in a formula in Excel, it’s not going to magically work here.

Yeh I tried it to generate an email, it did its best but that needs vba as I suspected. Thought I'd give it a go in case I missed something when I tried this before and had to use vba for it. Useful for eliminating possibilities as well as finding them.

You can't do that as a pure formula, you would need to use conditional Formatting>New Rule>Use a formula to determine which cells to format.

and the formula that was outputed could be that formula (just need little tweaks(

Yah, the correct formula would be =A1<0

You would then have to set up the range and formatting through the conditional formatting interface.

You can't use a formula to change a cell's background color. At least not prior to office 360 (but I haven't heard of them adding that), you need conditional formatting.

Conditional formatting uses formulas.

The formula used must resolve to True or False.

They can, but don't have to.

Home >> Conditional Formatting >> New Rule... >> Format only cells that contain

Set to "less than" and then enter 0. Format as desired.

The formula it outputed could actually be used as the conditionnal formating formula (just need to généralisé to a range of cells instead of only one, but op asked for "the" cell so A1 was a good answer)). I am actually impressed.

Nah, not useful.

Calculate the sum of the fourth column and fifth column and display YES in the sixth column for each sum that is more than ten

Result:

=SUM($D4,$E4) > 10

That's not how Excel formulas work. You have to put them somewhere. And you can't make a formula fill in some random cell somewhere else. Try this:

Print yes if sum of column d and e is greater than 10

And the output is

=IF(SUM(D2:E2)>10,"yes","no")

If you copy this into F2 you get exactly what you want.

Fair point, but in that instance, I'd expect it to show multiple outputs to use.

you just don't know how to explain things logically. it's okay dude.

Calculate the sum of results in the fourth and fifth columns. If their sum is greater than 10 return yes or no in column 6. Gets you a little closer.

Wow! This is really useful.

Thanks - this solved a problem I’d been trying to figure out

Following excel bot formula

Nice!

How many Easter eggs did you put in? This is a tremendous tool

Does it consider spill functions? These are probably the best thing in excel in a long time.

It appears to give random options to the same question - noticed when I accidentally asked "Please do not randomly assume everything is a date" a couple of times

Alright, bot! Do my exam.

I’ve never saved a Reddit post faster

Awesome thanks so much

Coolest thing ever

Neat!

Wouldn't matter.. The computer work gives me locks up for 15 minutes everytime I use anything other than a basic xlookup....

Cool...I'll have to give this bot a try...But - and I am only saying this because I care - there are a lot of decaffeinated brands on the market today that are just as tasty as the real thing.

Find the hottest girl in a column of girls.

=MAX(A:A)

Aaaaaaaaaayyyyyyyyy.

I have a problem it can't solve. I use a formula for mail merges. This formula makes a list of employee names separated by a semi-colon if they have the same manager, that way each manager doesn't get a separate email for each employee.

In other words, it uses TEXTJOIN to make a list of values if they share a value in a different column

=TEXTJOIN("; ", TRUE, IF(A:A=A2, B:B), "")

Where col A has the recipient email and B has the employee names to join into a list.

I couldn't get it to come up with a working formula.

There's probably an easier way but could you add a third column and include the supervisor then have it get rid of duplicates? Or use a vlookup function possibly.

Yeah, with this method you do have to remove duplicates manually at the end. You also have to sort by manager name so that they're all grouped together since the formula is sequential.

I think the tricky thing about trying to use VLOOKUP or INDEX(MATCH()) would be that a manager could have anywhere between like 1 and 40 employees under them - so I couldn't figure out how to get the formula to return a variable number of items & put them into a list. Like, a for loop in python or something could handle it, but idk how to do something like that in excel in a way that wouldn't require those other steps (without a VBA macro anyway).

Fair enough. Excel does have its limitations unfortunately.

It works!

I wanted a formula to convert hex colours to HSV. But the AI keeps suggesting functions that don't exist like =HEX2HSV(A1) or =HSV(HEX2DEC(LEFT(A1,2)),HEX2DEC(MID(A1,3,2)),HEX2DEC(RIGHT(A1,2)))

What doth life

Handy I guess but I kinda like looking up how to do stuff and playing about with formulas. Then breaking them in Google Sheets and finding the alternative.

I put in "sort two columns" and "sort by two columns" and it didn't return the "SORTBY" function, couldn't be more clear with the hint

Amazing tool! Thank you!!

This free AI bot sounds kind of awesome

There is even a bot that writes you REGEX.

9mm MMB m ng h MMB vgbmmn NM b ds d s

Could you do something like this for Numbers/Mac?

Nice will check this out.

Since excel is turing complete, this could in theory create a program creating excel formulas ad infinitum.

Amazing find!

Hi, for something like this:

"When column A equels the word 'Yes', count if column N says VIC"

It gives

=COUNTIF(A:A,"Yes",N:N,"VIC")

This is very close to correct, but should be countifs, not countif.

nvested $50,000 in a CD 10 years ago that paid 6.25% per year

compounded monthly. Write an Excel formula or function in the Financial worksheet to determine the

current value (FV) of the CD and put your cell values and calculation for the financial function in row 3

in cell C3. Highlight your current value in yellow. Does the CD accrue enough to pay for the expansion

- TRUE or FALSE? Put the Excel formula or function calculation that results in TRUE or FALSE in cell

I3 and a description of the outcome in cell J3.

Well this takes me back to highschool days, I never learned shit then, it's still the case today 🤭

if c2 = f7 write the value of c3 in f9

who else came to see this post, after the tool became successful