yet 2 more Excel questions

Factual Questions
1

I need to know how to do the following if it isn’t too complicated.
I have a spread sheet where it has two values multiplied and the result is shown in a third cell. It is for calculating commissions on sales. Example item A pays a $5 commission, Item B pays a $10 commission, etc.
So I have an employee’s name and the number of each item on that row. Down at the bottom of the sheet I have the per item commission listed. These two cells are multiplied together and the result is shown on the row directly below the number of items row.
Since I probably didn’t explain that very well it looks like this:



         wigits       whatsits     thingamagigs   gizmos
Bob          5               0               10               0
           $25.00        $0.00          $20.00           $0.00

Value     $5.00      $10.00          $2.00            $15.00
per item

What I want to do is to suppress the zero dollar values So in the columns for whatsits and gizmos, since Bob did not sell any rather than have Excel display $0.00 I would like to have a blank cell.
How do I do this?

Second question:
I have a very wide spread sheet (about 3-4 pages wide) The far left column is employee names. I can lock the left column so that it does not scroll on my screen, but can I set it up so that when I print, the names will appear on the far left column of each page?

2

Let’s say the cell for Bob’s whatsits commission (say it’s $7 per whatsit) is:

=c2*7

Replace it with:

=if((c27)>0, c27,"")

So, if c27 is greater than zero, it’ll display c27. Otherwise, it displays a blank (a pair of double-quotes with no space between them - not clear in this forum font!)

3

I don’t know. By formatting the cell as an accounting number it will display $-, if that is any better.

In Page Setup go to the Sheet tab. There you can choose “Columns to repeat at left”, i.e. the columns to print on every page.

4

For the second question assuming Excel 2003 or earlier, highlight the first cell in the column immediately to the right of the names go to Window->Freeze Panes.

5

For printing the far left column on every page, choose File, Page Setup, the Sheet tab. In the “Columns to repeat at left” box, enter $A:$A. (This is in Excel 2002, so YMMV.)

6

That affects the on-screen display, but not the printed output.

7

Oh, whoops. I thought he wanted to do both. Missed a word in his OP. And important word, it turns out…

Uh, let’s just let that be a bonus tip for those of us reading at home who don’t know how to freeze panes…

8

Ladies and Gentlemen, we have our winners. Thank you all so much for the assist.

9

Hey no harm, no foul. :smiley:

10

Actually, there is a better answer to the first question.

You can do the formula as Early Out suggested. But since it doesn’t include a value, it will screw up some other formulas you might eventually want to use (like =AVERAGE). The better solution is to change to a custom format that will repress showing anything for a value equal to zero.

To do that, go to Format/Cells. For this example, I will assume you had previously selected “Currency” with the sign and two decimals. Now click on Custom. In the type field, you should see "#,##0.00". Now add to that to read “#,##0.00;(#,##0.00);” (without the quotes). The format before the first semicolon is for positive numbers, between the semicolons is for negative numbers, and after the second semicolon for the value zero. Notice there is nothing after the second semicolon. That’s the trick.

11

I agree about not using"" to insert a blank space because it is a bad habit to get into. I didn’t know you could do this in formating. I always just turned off zero’s using the custom set up.

12

I was just playing with the above formating and added ;“big honkin zero”

It added the text in place of zero’s and the cell is still treated like the number 0.

You just know I’m going to use that as a practical joke on someone’s spreadsheet.