How do I leave a field blank when it's a total?

[Guest]

For example:

I have 4 columns with values 1 2 3 4
+ 1 2 3 4
A B C D
6 8

I want to show only values
that are higher than 5 as a sum of the numbers vertically for A B C D.

On the final line, I want to have the totals calculated. I'd have 6 for C and 8 for D, but I don't want anything in the blocks for A and B. Nothing, no zero, no symbology, nothing, long as my qualifier is > 5, I don't want the field populated.

Is there an easy way to show this?

Thanks

 

[Barrie Davidson]

On 2002-03-06 12:46, Anonymous wrote:

For example:

I have 4 columns with values 1 2 3 4
+ 1 2 3 4
A B C D
6 8

I want to show only values
that are higher than 5 as a sum of the numbers vertically for A B C D.

On the final line, I want to have the totals calculated. I'd have 6 for C and 8 for D, but I don't want anything in the blocks for A and B. Nothing, no zero, no symbology, nothing, long as my qualifier is > 5, I don't want the field populated.

Is there an easy way to show this?

Thanks

Would it be okay if you had the totals in columns A and B but couldn't see them (i.e., A total = 2 and B total = 4)?

How about if you zeroed out the total if it was less than, or equal to, 5 and then custom formatted it so the zero wouldn't show?

The reason I ask these questions is I am wondering what you are doing with the totals.

Regards,

 

[Guest]

=if(sum(**:**)=0,"",sum(xx:xx))

 

[croweld89]

Question regarding the above response

=if(sum(**:**)=0,"",sum(**:**))

The "" formats the cell as a text cell and the result can not be used in further calculations. Is there an alternative?

Thank you for your time
-Dave

 

[Barrie Davidson]

On 2002-03-06 14:22, croweld89 wrote:
Question regarding the above response

=if(sum(**:**)=0,"",sum(**:**))

The "" formats the cell as a text cell and the result can not be used in further calculations. Is there an alternative?

Thank you for your time
-Dave

Sure, how about
=sum(**:**)

You can then custom format the cell so the zero doesn't show. Use this for formatting (including the quotes):

0.00;-0.00;""

If you have any questions just let me know.

Regards,



_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-03-06 14:26

 

[croweld89]

Thank you Barrie, however...

My goal is to automate a 25+ worksheet file. Most of the worksheets contain columns of one years worth of months. To meet this goal, I don't want to manually hide and unhide months that are not populated for the current year. Hence, I am using the "IF" statement.

Is there an alternative way to automate hiding cell contents when the cells contents equals zero that will automatically unhide when not equal to zero? And, allow the cell to be used in further calculations even when the cell is hidden?

Thank you.
-Dave

 

[Barrie Davidson]

On 2002-03-06 14:34, croweld89 wrote:
Thank you Barrie, however...

My goal is to automate a 25+ worksheet file. Most of the worksheets contain columns of one years worth of months. To meet this goal, I don't want to manually hide and unhide months that are not populated for the current year. Hence, I am using the "IF" statement.

Is there an alternative way to automate hiding cell contents when the cells contents equals zero that will automatically unhide when not equal to zero? And, allow the cell to be used in further calculations even when the cell is hidden?

Thank you.
-Dave

Dave, that formatting will display nothing if the cell contents equal zero. Give it a try - right click on cell A1, select format cells, select Custom on the Number tab and paste this in the Type box

0.00;-0.00;""

You will see nothing if you put zero in cell A1 and you will see the number if you put anything else in.

Is this any clearer?

 

[croweld89]

Thank you Barry!!

I will need to tweak my file a bit for this, but your recommendation will work. But, this should do the trick. Thank again.

-Dave