# Formula questions

#### gschultz

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
To make the \$0 disappear,
=if(formula=0,"",formula)

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

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

I assume you want blanks where the answer is zero:

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

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

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

gschultz said:
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.

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.

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;""

Replies
38
Views
972
Replies
8
Views
161
Replies
6
Views
165
Replies
2
Views
151
Replies
1
Views
313

1,217,673
Messages
6,137,916
Members
450,098
Latest member
Ikmal Sabri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back