How can I replace zeros with blank spaces during calculations plea

[Guest]

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(Z8>0.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

 

[Guest]

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.

 

[Biff]

Hi!

Try this:

=IF(Z8>0.5,Z8,"")

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

Biff

 

[Guest]

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))

 

[Guest]

Hi, thanks for the quick responses. Although, sorry but I’m 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.

 

[Guest]

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.

 

[Guest]

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(E3>0.5,E3,"")

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

Any suggestions please?

Ted

 

[Randall.Senn]

Is cell E3 the result of C3 minus D3?

 

[Guest]

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???

 

[Bob Smith]

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

Bob

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