» Site Navigation |
|
|
|
» Stats |
Members: 1,367
Threads: 270,916
Posts: 1,021,555
Top Poster: Simon Lloyd (1,646)
|
| Welcome to our newest member, malstan |
|
|
» Today's Help |
|
| | Sponsored Links |
online casino bluebook - online casino gambling guide, to top ranked online casinos and reviews of over 200 gambling related websites. www.onlinecasinobluebook.com also host a casino forum, blog, and casino news articles
|
Working with functions (Basic)
Now that you know how to start a formula and have a very basic knowledge of them we will discuss and example a few of the different functions.
You have already met SUM where you can unconditionally total all the cells in your given range, however it has a more versatile counterpart SUMIF, and yes it is as it sounds SUM IF, add something if something is true, take a look at the picture:
As you can see we have 20 items in column A and 20 numbers in column B if this was a simple SUM our answer would be 30, but because of the power of SUMIF we can just total those numbers that match our criteria in this case its "February"
Our formula is written in this fashion =SUMIF(Range, Criteria, Sum Range) so we used =SUMIF(A1:A20,"February",B1:B20), you don't have to stick to adjacent ranges or even different ranges take a look at this =SUMIF(B1:B20,1,B1:B20) this will give a total of 10 as there are ten 1's.
Moving on we have the following functions COUNT, COUNTA, COUNTIF, COUNTBLANK, the difference here is these don't total what's in a cell they merely count the cells, COUNT merely counts the amount of cells that contain numbers, COUNTA will count all cells that contain either a number or text (or both), COUNTIF works the same as our SUMIF, but only COUNTS cells that match our criteria and only works on one range like this =COUNTIF(A1:B20,"February") and will return 7 and COUNTBLANK....well you can work that one out. |
| Previous Help | Previous Apperances
User Defined Function (UDF) in VBA |
Building your first formula
All formulae must start with an equals symbol =, they mostly also contain open and close parenthesis ()
This =A1+B1 is the same as =(A1+B1), you will find that in future you will always build your formulae using the (), ALL functions use the parenthesis.
Lets look at summing many cells, you could do this =A1+B1+C1+.......H1 or you could wrap them in the () whichever way would be the same, however the smarter easier to read version would be =SUM(A1:H1), using SUM in our formula tells Excel that we want to add up each cell in that range, using this neat way not only looks smarter but has advantages over the longer version without parenthesis when manipulating the formula like this =SUM(A1:H1)/2+3*2 in this example we first find our total for the range and then perform mathematical manipulations to that total.
As your formulae will probably be longer than that it is useful to understand an important function, take our example =SUM(A1:H1)/2+3*2 without manually adding up the values in A1:H1 you would have no idea what value you are dividing by 2, adding 3 and multiplying by 2!
A neat trick is to highlight parts of your formula like =SUM(A1:H1) in your formula bar and then press F9 you will then see the value change from =SUM(A1:H1) to the actual total, so in my case it would be 36/2+3*2 which gives the result 24.
One thing to note with using F9 to evaluate a formula or part of a formula is, if you were to hit ENTER the evaluated part of the formula becomes that static evaluated figure which in my case would be 36, the way to combat this is don't press ENTER but hit ESC instead
Anything I have misquoted, misunderstood or simply missed feel free to correct me or supply a submission for entry to these pages, raise a ticket here at our Helpdesk and let us have your suggestion.
|
|
» Donation Support |
| Your Donation Will Be Used To Pay For our ever increasing bandwidth costs, our hosting Service, domain registration, software licensing fees, and maintenance costs |
Site Costs
[ Loading Progress Bar ]
Received £0.00 with 0 contributors Ends February 23rd, 2010
|
 |
|
» News |
| I am new. |
|
Feb 02, 2010 - 9:11 AM - by nevaeh.aaric
|
Hello,
Friends this is Nevaeh.
I am newbie here and this is my first visit also. I am here to get some knowledge about SEO. I am very happy to join this forum
I am actually looking forward to learning more about SEO.
... [Read More]
|
75 Views
|
| Forum progress news! |
|
Jan 26, 2010 - 2:06 PM - by Simon Lloyd
|
|
Thanks to all our visitors and members The Code Cage is doing well, we are now at Page Rank 3 ( see at the bottom of our Homepage) and our ... [Read More]
|
|
74 Views
|
| Excel - Simple Conditional Formatting |
|
Jan 23, 2010 - 8:55 AM - by Simon Lloyd
|
|
Top Tip Submitted By: Simon Lloyd
... [Read More]
|
|
106 Views
|
| Wrapping your code submissions |
|
Jan 22, 2010 - 7:58 PM - by Simon Lloyd
|
When posting code to illustrate your problem or provide a sample of what you're using we ask that you wrap your code in code tags, this makes the code easier to read and follow.
You can wrap your code in one of 3 ways:
1. you can... [Read More]
|
|
90 Views
|
| Excel - Run macro in 2 parts with same button |
|
Jan 20, 2010 - 5:35 PM - by Simon Lloyd
|
A new entry has been added to Code Cage Downloads, category Excel VBA
Description: On first click of a command button run one section of code, on second click run second section.
Ever wanted to use the same command... [Read More]
|
|
95 Views
|
| Excel - Use Option Explicit |
|
Jan 07, 2010 - 12:43 PM - by royUK
|
|
Top Tip Submitted By: royUK
... [Read More]
|
|
149 Views
|
| Excel - remembering hidden columns |
|
Jan 07, 2010 - 2:45 AM - by teylyn
|
|
Top Tip Submitted By: teylyn
... [Read More]
|
|
100 Views
|
| [SOLVED]: Welcome suggestions on making a Matching Quiz! |
|
Dec 23, 2009 - 9:25 AM - by charlene
|
|
Hi, have anyone ever made a Flash matching quiz? I think it is wonderful to make such a quiz for assessment, learning and fun. Could you give me some suggestions on it?
|
|
294 Views
|
|
» The Code Cage Ranking |
| Visitors To This Page |
|
|
Free Microsoft Office Help for all Applications!
The Code Cage - Microsoft Office Help - Microsoft Office Discussion
