Formula questions

[gschultz]

Hi, I have two questions.

First, let me explain the framework. I am creating a one sheet document with several simple calculations except one.

I am utilizing 38 rows on the sheet, row 1 is the column names, rows 2-37 are for data, and row 38 is for totaling. I have a situation where 2 columns of data are used to create a third column's data, but if i don't use all 35 available cells(because not all 35 are needed at that time) a "$0" appears in the cells with no associative data.
My first question is how to make those "$0" disappear if no data is present.
(Example: I only have data in rows 2-15, but the column that contains the formula shows "$0" in rows 16-37.)

My second question is a little harder for me to explain. I have a column that I want the formula in row 38 to not be a basic math computation(+,-,*,/) of the previous 35 cells, but instead an average. This isn't to hard normally; my first thought was to right the formula as =(SUM(G2:G37))/35, but realized that if I only had data for 15 cells for example and not 35, the computation would be incorrect. How can I create a formula that divides by the number of cells with actual data instead of the possible data range.
(Example: I only have data in rows 2-15, so I want the average to be the sum of data in those cells divided by 13, not 35.)
Thank You

 

[tactps]

To make the $0 disappear,
=if(formula=0,"",formula)

For average:
=average(G$2:G$37)

 

[gschultz]

Thanks the average worked great.
One more question, if i already have a formula in a cell,
(Ex: =(C25*G25)), can I also have an "IF" formula, and if yes, how?
Thanks again

 

[tactps]

I assume you want blanks where the answer is zero:

=if(c25*g25=0,"",c25*g25)

 

[gschultz]

Perfect, sorry i did not understand the first response.
Thank you so much for your help tactps, I appreciate your time and consideration.
Geoff

 

[gschultz]

Ok, as I complete my work, I am getting an error in a column "F" that has the folowing cell dependant formula:
=IF(E10/E38=0,"",E10/E38)
The "E" cells have this cell dependant formula:
=IF(C10*G10=0,"",C10*G10).
The "C" and "G" cells are all independant data.

For the cells that an "E" value is shown the "F" formulas works fine, but when I get to row 15 for example where no "C" or "G" data is available, the cell E15 shows nothing based on the above "if" formula. The result is error "#VALUE!" in cells F15-F37.
Any way to correct this?
Thanks

 

[Brian from Maui]

Ok, as I complete my work, I am getting an error in a column "F" that has the folowing cell dependant formula:
=IF(E10/E38=0,"",E10/E38)
The "E" cells have this cell dependant formula:
=IF(C10*G10=0,"",C10*G10).
The "C" and "G" cells are all independant data.

For the cells that an "E" value is shown the "F" formulas works fine, but when I get to row 15 for example where no "C" or "G" data is available, the cell E15 shows nothing based on the above "if" formula. The result is error "#VALUE!" in cells F15-F37.
Any way to correct this?
Thanks

First to not display zero values, you can go to Tools/Options/View tab and untick Zero values.

Usually when you get a #VALUE! error you have text where the data should be numeric.

Maybe it's me, but your explanation gets very confusing. Can you post some sample data? What data is entered in what specific cell and what cell houses formulas.

 

[tactps]

It sounds like you have formulas dependant upon the formulas we changed.

You either need to continue the trend, by changing (say):
=E16+E17 where E16 is now blank with:
=if(iserror(E16+E17),"",E16+E17)

or as Brian rightly points out, you may be better just supressing your zeros and changing your formulas back.

 

[tactps]

Another neat way (if you don't want to supress all zeros) is to apply a format to the cells.

Under the Format Menu, Choose Cells
Choose custom and paste this in:

0.00;-0.00;""