Formula questions

gschultz

New Member
Joined
Jan 17, 2005
Messages
4
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
To make the $0 disappear,
=if(formula=0,"",formula)

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

gschultz

New Member
Joined
Jan 17, 2005
Messages
4
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I assume you want blanks where the answer is zero:

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

gschultz

New Member
Joined
Jan 17, 2005
Messages
4

ADVERTISEMENT

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

New Member
Joined
Jan 17, 2005
Messages
4
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

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.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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;""
 

Forum statistics

Threads
1,148,525
Messages
5,747,194
Members
424,068
Latest member
Salim khamis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top