How can I replace zeros with blank spaces during calculations plea
 

I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations plea
 

Ted,

The SUMIF function may do what you need.

Here's an example.

In cells A1 through A5 type the numbers 1, 2, 3, 4, and 5 respectively.

In cell A6 try this formula to sum the numbers less than 3 (i.e., 1 and 2).

=SUMIF(A1:A5,"<3",A1:A5)

You can modify it for other results.


"Ted" wrote:

I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations plea
 

Hi!

Try this:

=IF(Z80.5,Z8,"")

That will leave the cell "blank". Not to be confused with EMPTY.

Biff

"Ted" wrote in message
...
I am trying to get excel to sum totals, but exclude answers that are
outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than
a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations plea
 

Ted,

Sorry, I missed part of your question.

Modify the formula in cell A6 like this to show blanks.

=IF(SUMIF(A1:A5,"<3",A1:A5)=0,"",SUMIF(A1:A5,"<3", A1:A5))

Try it with less than 1, instead of less than 3, to see the blank result.

=IF(SUMIF(A1:A5,"<1",A1:A5)=0,"",SUMIF(A1:A5,"<1", A1:A5))

"Ted" wrote:

I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations
 

Hi, thanks for the quick responses. Although, sorry but Im not sure that I
explained my situation very well: I am trying to subtract one set of figures
from another (e.g. A1:A5-B1:B5). When the answer falls out side of a certain
range (e.g. any answers is below, say, 4), I want to exclude them from all
further calculations. The reason why I cant have zeros appearing in the cells
with the answers is because I then run a correlation on the data €“ meaning
that it counts the zeros as data, altering the means an stuff. The only safe
way I can get it to run the correlations and alike, is if the cells contain
either the data/numbers I want to include in the correlation, or they are
completely blank. I need cells to be completely blank after other
calculations too!

Any ideas please??

Thanks again, Ted.


"Roland" wrote:


Ted,

Sorry, I missed part of your question.

Modify the formula in cell A6 like this to show blanks.

=IF(SUMIF(A1:A5,"<3",A1:A5)=0,"",SUMIF(A1:A5,"<3", A1:A5))

Try it with less than 1, instead of less than 3, to see the blank result.

=IF(SUMIF(A1:A5,"<1",A1:A5)=0,"",SUMIF(A1:A5,"<1", A1:A5))

"Ted" wrote:

I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations
 

Hi, sorry - I think the suggestion from Biff may be working - I'm not sure if
this will work (using "" rather than 0) in all the ways I need it to though,
so would still appriciate suggestions.

thanks for all of your help, and thanks Biff for this quick fix $;-)

Ted.

"Biff" wrote:

Hi!

Try this:

=IF(Z80.5,Z8,"")

That will leave the cell "blank". Not to be confused with EMPTY.

Biff

"Ted" wrote in message
...
I am trying to get excel to sum totals, but exclude answers that are
outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than
a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations
 

Ok, does anyone know how I can now add these two together, so I can run it
all in one cell please? I have added the €œ€� to plug the zero problem, but are
unsure how I would add these two:

=IF(E30.5,E3,"")

=ROUND(7/(C3-D3),5)

Any suggestions please?

Ted


"Biff" wrote:

Hi!

Try this:

=IF(Z80.5,Z8,"")

That will leave the cell "blank". Not to be confused with EMPTY.

Biff

"Ted" wrote in message
...
I am trying to get excel to sum totals, but exclude answers that are
outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than
a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations
 

Is cell E3 the result of C3 minus D3?

How can I replace zeros with blank spaces during calculations
 

I AM TRYING TO KEEP A RUNNING TOTAL ON A FORM. tHE PROBLEM I AM HAVING IS
THAT WHEN I EXTEND THE FORMULA TO A ROW, IT CONTINUES THE TOTAL ALL THE WAY
DOWN THE COLUM EVEN IF THERE IS NO VALUS IN THE SPOT BEFORE IT. I CAN SEND
OVER THE FORM IF IT WILL HELP EXPLAIN. THE CURRNT FOMLA IS SUM(I4+H5) BUT IF
H5 IS "0" THEN I DO NOT WANT IT TO TOTAL???

"Ted" wrote:

Ok, does anyone know how I can now add these two together, so I can run it
all in one cell please? I have added the €œ€� to plug the zero problem, but are
unsure how I would add these two:

=IF(E30.5,E3,"")

=ROUND(7/(C3-D3),5)

Any suggestions please?

Ted


"Biff" wrote:

Hi!

Try this:

=IF(Z80.5,Z8,"")

That will leave the cell "blank". Not to be confused with EMPTY.

Biff

"Ted" wrote in message
...
I am trying to get excel to sum totals, but exclude answers that are
outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than
a
zero because it alters future calculations. Has anyone any ideas please??

Ted

How can I replace zeros with blank spaces during calculations
 

Have you gone into Tools - Options and unenabled Zero Values yet?

Bob

ps: Please don't post in caps as it's considered shouting.


"j.r." wrote in message
...
I AM TRYING TO KEEP A RUNNING TOTAL ON A FORM. tHE PROBLEM I AM HAVING IS
THAT WHEN I EXTEND THE FORMULA TO A ROW, IT CONTINUES THE TOTAL ALL THE
WAY
DOWN THE COLUM EVEN IF THERE IS NO VALUS IN THE SPOT BEFORE IT. I CAN SEND
OVER THE FORM IF IT WILL HELP EXPLAIN. THE CURRNT FOMLA IS SUM(I4+H5) BUT
IF
H5 IS "0" THEN I DO NOT WANT IT TO TOTAL???

"Ted" wrote:

Ok, does anyone know how I can now add these two together, so I can run
it
all in one cell please? I have added the "" to plug the zero problem, but
are
unsure how I would add these two:

=IF(E30.5,E3,"")

=ROUND(7/(C3-D3),5)

Any suggestions please?

Ted


"Biff" wrote:

Hi!

Try this:

=IF(Z80.5,Z8,"")

That will leave the cell "blank". Not to be confused with EMPTY.

Biff

"Ted" wrote in message
...
I am trying to get excel to sum totals, but exclude answers that are
outside
of a certain range. I can get it to do this by using
=IF(Z80.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather
than
a
zero because it alters future calculations. Has anyone any ideas
please??

Ted