*If and Then formulas*

Points | Max Points | Total |

65 | 50 | 50 |

35 | 50 | 35 |

46 | 50 | 46 |

So that column C will not exceed the max points as stated in B column, C column formula will be :

If(A2>B2,B2,A2)

which means, if A2 is greater than B2 then use B2, otherwise if A2 is not greater than B2 then use A2.

*Calculate an average of cells*

The formula is @AVG(A1..A10) (or what ever the range of cells are that you want to use for averaging.)

*Hiding Columns*

Click on the column to select it, then right click on it and choose Hide.

To reveal it back again, select the column before and the column after. Select Format, Selection Properties and put the dot in for Reveal

*<enter> to move or continue in cell*

To change this option from one style to another, go to Tools, Settings. In the section for Application/General, un-check Move Selection After Enter.

*Truncating instead of rounding off*

Changing 14.156667 to 14.15

Use the formula @int(A1*100)/100 where A1 is the field you are working with

*Bisecting A Cell*

To make a diagonal line through a cell, you first need to reveal your Drawing Tools toobar (at least for the moment). Right click on your toolbar, then select Drawing Tools

Click on the diagonal line tool, hold the <Alt> key and drag a line from corner to corner in the cell.

Line properties can be changed to use different styles or colors. Just right click on that line and choose Selection Properties, then click on the Line tab to change the color and make any other changes.

*Zero results value hidden*

If you don't want the zero's to show on a page, then click on Format, Sheet Properties, Display (tab), and click on No in the Display Zero section

*Column Head with multiple lines instead of multiple columns*

Alt+Enter or Ctrl+Enter just won't work here. You need to type the heading as usual in the cell you want to use.

With your cursor still in that cell, right click and choose Selection Properties, then click on the Alignment tab. Place a checkmark in the box for Wrap Text.

*Highest number in a range*

I have a time sheet that I use to record my hours, salary, tips, etc. In each 2 week sections I have a cell that tells me how much I averaged per hour for those 2 weeks. At then top of column C, the cell tells me what the most amount I have earned in a 2 week period (This is great when you're a waitress, or when you salary constantly changes from week to week.

To find the highest number in a range of cells, place your cursor where you want the result to show. Then for the formula type: @MAX(C10..C300)

Adjusting the C10 and C300 to the cell range you are using.

This result will show the highest number in the cells from C10 to C300...presuming all the cells are data cells and not text.

If there IS text in that range (C10..C300), then to have the results of this cell range without including text cess, you will have to type each cell individually: @MAX(C100,C200,C300)

*Runtime Error Fixed*

When Quattro Pro won't start because of the Runtime Error, a registry entry needs to be fixed:

In regedit, go to:

HKCU/Software/Corel/User Assistant/XX/Recent Work (where XX is your version of QuattroPro)

Remove the folder in there called Quattro Pro, but Leave the Presentations and WordPerfect folders

*Combining Text & Formulas in a single cell*

To have a single cell contain text and a formula, use the following as your example and make appropriate adjustments for the cell range.

The results for both of the following look like this: **63 October**

Example A:

This formula takes the total of AL367 through AL411, then adds the word October at the end of the calculation.

@STRING(@SUM(AL367..AL411),0)&" October"

Example B:

If AL367 through AL411 is already calculated in another cell (say at cell AL412), then you can use the AL412 cell instead as follows:

@STRING(AL412;0)&" October"

If you want the month first, then the code would change to:

+"October "&@STRING(@SUM(AL367..AL411),0)