If and Then formulas
So that column C will not exceed the max points as stated in B column, C column formula will be :
which means, if A2 is greater than B2 then use B2, otherwise if A2 is not greater than B2 then use A2.
Calculate number of rows with data, excluding blank lines
To calculate the number of rows that have data (text or formula), excluding blank lines
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.)
Calculate number of cells with specific data
To calculate the amount of cells in a column that have the word Bandanna in the cell, use this:
For multiple columns, you can use this:
Calculate number of cells with any data excluding empty cells AND have text to displayed
To have text AND to calculate the amount of cells in a column that have data and exclude empty cells within that same range, use this:
For multiple columns, use this:
Calculate totals from 2 different files
I want to take a total in a file called Sales.qpw from column B54 and add it to the total of N71 in a file called Business.qpw and give me a combined total.
Note: If you add rows to Sales.qpw, without the Business.qpw open, you will need to adjust the formula in P72 to reflect the change.
Example: Sales.qpw cell B54 plus Business.qpw cell N71 and give me a combined total in Business.qpw P72
Open both of the files you want to have used and click on the appropriate sheet (if you have multiple sheets).
Be on the file where you want the combined total.
In the cell where you want the total displayed (in this case P72), use this calculation:
If the Sales.qpw is in a different folder on your computer than the Business.qpw, you'll need the full path of the file:
If the Sales.qpw is in the SAME folder as the Business.qpw, use just the file name
That will add Sales.qpw, sheet 2019, cell B54 and add it to Business.qpw, sheet 2019, cell N71, and give the calculation in cell P72
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
This formula takes the total of AL367 through AL411, then adds the word October at the end of the calculation.
If AL367 through AL411 is already calculated in another cell (say at cell AL412), then you can use the AL412 cell instead as follows:
If you want the month first, then the code would change to:
When the formula doesn't use a 'String', but just adding the amount of rows with data in a column (and excluding blank cells in the column), the formula changes a little bit
Sorting Columns (for PayPal statements)
Sorting is default by oldest date first, but to sort the rows so the last date is first.
Change the A column to date format
Click on the last entry of the last column, then hold the shift key and click on A1 to select the entries
Click on Tools / Sort
Change "1st" block and type A1 and Uncheck Ascending & uncheck "Selection contains a heading"
Click on Sort.