I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

On some of my spreadsheets that use a bunch of formulas i use conditional
formatting to hide the zeroes:

Format Conditional Format:

Cell Value is: Equal to: 0

and my text color is white.

The cell value will remain 0, but for display purposes it'll show up blank.

Now if you're looking for a code to replace a returned value of 0 for
another value, then that's a different story.



"Newfie809" wrote:

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

Example:
=IF(LEN(VLOOKUP(A1,B1:C10,2,FALSE))=0,"",VLOOKUP(A 1,B1:C10,2,FALSE))

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Newfie809" wrote:

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

Hi Adam,

What I am looking for is the cell returns nothing. When something is in the
cell it will count as a .33 I have 4 columns and in each cell of the 4
columns there is a formula that will return a .33 and that will add up to 133
and I only want it to add up to 100 therefore the cell should return with
nothing because even with the zerro the cell thinks there is something there
and it will return a .33. I don't know if I amy explaining it correctly do
you understand what I am trying to do. I could delete the zerro but then I
am deleting the formula also and I need the formula there, just in case on
the other spread sheet the information changes in each column.


Thank you for your response.


--
Newfie


"Adam" wrote:

On some of my spreadsheets that use a bunch of formulas i use conditional
formatting to hide the zeroes:

Format Conditional Format:

Cell Value is: Equal to: 0

and my text color is white.

The cell value will remain 0, but for display purposes it'll show up blank.

Now if you're looking for a code to replace a returned value of 0 for
another value, then that's a different story.



"Newfie809" wrote:

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

A cell with a formula can never return "nothing".

You can make it look blank by trapping

=IF(A1="","",A1)

Where have you arbitrarily set a cell to return .33 when not completely
blank?

I think that is where you should be doing the correction.


Gord Dibben MS Excel MVP


On Fri, 16 Oct 2009 12:33:01 -0700, Newfie809 wrote:

Hi Adam,

What I am looking for is the cell returns nothing. When something is in the
cell it will count as a .33 I have 4 columns and in each cell of the 4
columns there is a formula that will return a .33 and that will add up to 133
and I only want it to add up to 100 therefore the cell should return with
nothing because even with the zerro the cell thinks there is something there
and it will return a .33. I don't know if I amy explaining it correctly do
you understand what I am trying to do. I could delete the zerro but then I
am deleting the formula also and I need the formula there, just in case on
the other spread sheet the information changes in each column.


Thank you for your response.

Hi Gary,

It returned the cell and it looks blank, but it still count the cell as if
there is something there and the cells that had something in them are now
also blank the infromation that was there is now blank. But thank you for
your help it solve part of the problem.




--
Newfie


"Gary Brown" wrote:

Example:
=IF(LEN(VLOOKUP(A1,B1:C10,2,FALSE))=0,"",VLOOKUP(A 1,B1:C10,2,FALSE))

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Newfie809" wrote:

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

Hi Gary,

Just to let you know that I got the formula to work as for bringing back the
information in the cell, and it works now. But the blank cell still acts as
if there is something in the cell and counts it in the total.

thanks again for your help
--
Newfie


"Newfie809" wrote:

I have a spread sheet with about 300 formulas and the formula is looking up
information and returning the answer from another workbook, but some of the
cells that do not have any information the fromula returns a "0" is there a
macro or a formula that if there is no information in that cell it is
returned blank?

thank you for you help
--
Newfie

Yes, I think you are correct. I ws thinking about it last night and decided
to try something in the sum column.

B C (D E F) G

ENG4C1 ENG3U1 ENG3U1 0 0 0 = 1.33

Here is the formula that I was using to add the 6 columns.
=ROUND((COUNTIF(B8,"<")+COUNTIF(C8,"<")+COUNTIF( G8,"<")+MIN(COUNTIF(D8:F8,"<"),1))/3,2.
So I need to adjust this column to get the result I would like.

thanks

"Gord Dibben" wrote:

A cell with a formula can never return "nothing".

You can make it look blank by trapping

=IF(A1="","",A1)

Where have you arbitrarily set a cell to return .33 when not completely
blank?

I think that is where you should be doing the correction.


Gord Dibben MS Excel MVP


On Fri, 16 Oct 2009 12:33:01 -0700, Newfie809 wrote:

Hi Adam,

What I am looking for is the cell returns nothing. When something is in the
cell it will count as a .33 I have 4 columns and in each cell of the 4
columns there is a formula that will return a .33 and that will add up to 133
and I only want it to add up to 100 therefore the cell should return with
nothing because even with the zerro the cell thinks there is something there
and it will return a .33. I don't know if I amy explaining it correctly do
you understand what I am trying to do. I could delete the zerro but then I
am deleting the formula also and I need the formula there, just in case on
the other spread sheet the information changes in each column.


Thank you for your response.

.