formulas with blanks and zeros

[rebraku]

Hi all,

I am working with questionnaire data. Each row is a participant, each column is an individual item. The rightmost columns are questionnaire subtotals, i.e., they contain formulas summing particular groups of items. I have set up the worksheet so that as I enter data, subtotals are automatically calculated. I have two issues.

1) Is there a way to prevent rows that do not yet have data entered from returning zeros in the subtotal columns? I have a worksheet with no data entered yet, but all the cells in the subtotal columns return zeros. Since zero means something different than blank in this dataset, I do not want the default to be zero, but rather blank.

2) What is the best way to get excel to treat blank cells as blank, not zeros? For individual items, I need to be able to enter the value zero when the participant answered zero, but leave it blank when there is no data.

Again, my formulas are simple sums, e.g.,
=SUM(AR2,AW2,AY2,BG2)

Many many thanks for your help!

[corinereyes]

hi rebraku,

Not sure if this is what you want but you can try this formula.

Please Login or Register  to view this content.

[arthurbr]

1) Is there a way to prevent rows that do not yet have data entered from returning zeros in the subtotal columns? I have a worksheet with no data entered yet, but all the cells in the subtotal columns return zeros. Since zero means something different than blank in this dataset, I do not want the default to be zero, but rather blank.

Select Tools - Options - View Tab - Uncheck " zero values" - OK

[rebraku]

Hi, sorry, I don't think I explained very well! I'm pretty new at this. I have set up a workbook and others will be using it to enter data. There's no data in it yet, but I have included subscale formulas in the worksheet, so that as others enter raw data, subscale scores will automatically be calculated. The problem is that the subscale formulas are returning zeroes (since there is no data yet), when I would like subscale scores for unentered data to be returned as blank.

Is there a way to get excel formulas to treat blank cells (unentered data) as truly blank, and then if the actual raw value is entered as 0 to treat that as 0?

My raw data range is AQ2:BJ2, and my formula is =SUM(AQ2,AV2,AZ2,BC2,BE2,BH2,BJ2).

Thanks so much!!