Excel peeps

[jimmy_onions]

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?

[Bewster]

Have you assigned a shortcut key ?

[Flashheart]

Sorted. Cheers peeps.

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

[jimmy_onions]

just out of curiousity, what is the said eqn?

[Flashheart]

just out of curiousity, what is the said eqn?

=LEN(F6)-LEN(SUBSTITUTE(F6," ",""))+1

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

[jonny72]

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.

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.

[Bewster]

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

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

[Jimmy Glass is an Alien]

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.

[@MacPhlea]

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

[Bewster]

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 ?

[jonny72]

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

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