Formulas

Resolved · Low Priority · Version 2003

Wed 7 May 2008, 16:41 Delegate Leona said...

Leona has attended:
Excel Introduction course

Formulas

I would like to learn more about formulas as I am working on quality check sheets for my team and have a point system but I keep having problems with the calculation. The 'IF' formula is my current problem.

Wed 7 May 2008, 20:14 Trainer Tracy said...

RE: Formulas

The IF function is based on a true or false situation, for example

=IF(A1>100, "Above target", "Under target")

dependent on whether the first condition (A1>100) is true the function will reveal the word Above target, and if is not true it will reveal the word Under target.

You have to put speech marks in if you want to reveal text, however if you want to put numbers you can leave the speech marks out.

For example

=IF(A1<1000, 20, 10)

So this would be interpreted as if the contents of A1 were less than 1000 it will give the number 20(i.e if the condition A1<1000 were true), if not it will give the answer 10.

Hope that helps,

Tracy

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.