Excel summing question

Factual Questions
1

It;s been a long time since I used Excel (v.2003) and these are probably easy questions.

  1. I have columns for sums and differences between various cells. Those data cells will be filled in every week as data is available. Until then, they have no data and zeros are shown for the various sums and differences. Is there a way to drop the displayed zero and just make the totals blank until numbers are put in the data cells? It would look better and might solve problem 2.

  2. For one column I am calculating the gain between 2 numbers each week in a column. For instance: this week’s total compared to last weeks. That is calculated as cell E2 contains: =sum(D2,-D1), the amount gained that week. That works fine, but cell E3 displays the difference between D3 and D2, and there is no data in D3 yet, so E3 shows the difference between zero and D2. I think if D2 was blank instead of zero, it would work.

Dennis

2

problem 2 can be solved by using some IF logic in your formula. For example: IF(D3=0,’ ',sum(d3,-d2)) or something similar.

This reads as: IF D3 = 0 then display a blank space otherwise display the difference between D3 and D2

3

Try


=if(isblank(C2:C30),"",sum(C2:C30))

for whatever range you need to sum. Check your docs for how the functions are set up, because my spreadsheet is similar to but not exactly the same as Excel.

4

That looks good, thanks. I’ll try it later, I’m off to lunch now.

Dennis

5

Regarding your first question, go to Tools - Edit - View. There will be an option shown on that screen to allow zero values to be shown as either an actual zero or as a blank cell. If there is a check in the “zero values” box, remove it and you should be good to go.

I’m glad to see another 2003 user. Thought I was all by myself.

6

That will blank all zero values anywhere on the sheet. Including places where your computed difference actually is zero. You may not want that.

If so, an option is to set a special format just for the cells where you want them to blank when zero. Highlight those cells, click format, cells, number, custom and enter “0;-0;;@” (without the quotes) in the box.

Another option is to use Conditional Formatting and for any cells that = 0, change the text color to white (or whatever color your background is). This leaves the zero in the cell, but makes it invisible – white text on a white background.

7

This worked perfectly. I kinda thought there was a box to uncheck but it sure is buried deep! The second problem is still there, but I think I can use a macro to check if the difference from previous weeks is the same as the current difference (which shouldn’t be there yet), then display a null instead of current value.

Dennis