Thetownend.com

This sounds like something that should have an easy answer, but I'm buggered if I can find it.

I have a formula in excel and I don;t want to have to type it in any time. How do I save it so I can just insert said formula into a cell whenever I need it.

Cheeeeeers

Just copy and paste it or drag it down through the cells...

If your formula includes a cell that is fixed in location (i.e. isn't relative to others then just preceed it with $ i.e. $A$3)

the other way os to create a macro button that inserts it when you click the button...

Well yeah.

But I want it to remain in excel even for use later on and in different workbooks. Excel already has formulas built in ready to use (sum, count etc). How do I make one of those myself? 

Are you familiar with macros?
You can do it as a macro, create a shortcut key (for example, ctrl, shft + Y) and save it in "new book", this will be available whenever you open a new sheet.

Click on record macro, give it a name and remember the shortcut key you choose.
Select "New book" in the "store macro in" field.
Highlight the text in the function text field, copy.

Done.

Open up a new workbook and do the shortcut key combo you chose, see if it worked?

Just tried to create a macro and managed to crash excel.

I'll crack it.

That image is different from mine, I'm using 2010 and don't get the 'Highlight the text in the function text field, copy.' option.

And boys are fantastic.

They're called functions, not formulas.

You can create your own but you need to write them in VBA.

Plenty of tutorials on the interweb.

You can also  set up a button on your tool bar and assign the macro to it

Something else for me to never get around to doing then

Ignore my suggestion, i've just closed excel and reopened and tried it, and it didn't work.

You need to save it in a hidden work book that sits behind excel.

When you record the macro you have to save it in the personal macro workbook.

Is visual basic the same language as when creating the formula (it looks as though it may be), or would I have to learn a new computing language?

This strikes me as something that should really be much, much, much simpler.

But nothing happens

Dunno if you can do this in excel, but in Mathcad, I have a standard template file which opens up when ever I open up a new work book (instead of just a blank one). I have all the eqns in there i need.

Is there such a thing as a excel templte file - if so, could you just stick your formula (sorry Jonny, function) in there?

Have you assigned a shortcut key ?

Sorted. Cheers peeps.

I'm going to get flash now and give it it's own button on the toolbar.

just out of curiousity, what is the said eqn?

It counts the number of words in a cell. Excel has no word count function so a formula has to be used instead.

VBA code to perform a word count here;

http://www.cpearson.com/excel/wordcount.htm

You'll just need to figure out how to make it available to every spread sheet. Again, Google is your friend.

Every day is a school day - didn't know that. Cheers Flash

Evening.

Is there anyway I can create a formula that when I colour a cell it adds the coloured cells up and totals them in a formula?

I want each cell to have a value of 0.5 and when I colour it green to total up for me.

Thanks in advance.

My first question would be... What criteria are you using to determine if it is green... If you can answer that question and it uses a standard formula then simply use that to trigger the inclusion... It'll save you the hassle of manually changing it to green each time.

Better still, chuck excel in the bin and build a database... Read this and you'll understand why I say that...

http://theitpartner.blogspot.co.uk/2012/11/spreadsheet-paralysis.html

You can filter by colour in excel and then sum that.

What I would do is in a hidden column put an "IF" statement that would return the a value if it falls in the set criteria and then use "sumif"

Let assume the the range that maybe green is D1:D10

So in your example in e1 put =if( d1= 0.5, "y", "n"), copy this down from e1 to e10.  then in your total put =Sumif(E1:E10, "y", D1:D10)

So if the value in e1:e10 = "y", then sum the corresponding vlaue in D1:D10. Make sense ?

Good article telling you everything you need to know about Excel colour functions here;

http://www.cpearson.com/excel/colors.aspx