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

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

Hi!

Try this:

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

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

Biff

"Ted" <Ted@discussions.microsoft.com> wrote in message
news:CA960450-92C4-407D-9B24-FEA1ACE91F42@microsoft.com...
>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
>

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

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.


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

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(Z8>0.5,Z8,"")
>
> That will leave the cell "blank". Not to be confused with EMPTY.
>
> Biff
>
> "Ted" <Ted@discussions.microsoft.com> wrote in message
> news:CA960450-92C4-407D-9B24-FEA1ACE91F42@microsoft.com...
> >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
> >
>
>
>

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


"Biff" wrote:

> Hi!
>
> Try this:
>
> =IF(Z8>0.5,Z8,"")
>
> That will leave the cell "blank". Not to be confused with EMPTY.
>
> Biff
>
> "Ted" <Ted@discussions.microsoft.com> wrote in message
> news:CA960450-92C4-407D-9B24-FEA1ACE91F42@microsoft.com...
> >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
> >
>
>
>

Is cell E3 the result of C3 minus D3?

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(E3>0.5,E3,"")
>
> =ROUND(7/(C3-D3),5)
>
> Any suggestions please?
>
> Ted
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > Try this:
> >
> > =IF(Z8>0.5,Z8,"")
> >
> > That will leave the cell "blank". Not to be confused with EMPTY.
> >
> > Biff
> >
> > "Ted" <Ted@discussions.microsoft.com> wrote in message
> > news:CA960450-92C4-407D-9B24-FEA1ACE91F42@microsoft.com...
> > >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
> > >
> >
> >
> >

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." <j.r.@discussions.microsoft.com> wrote in message
news:93200303-1681-4097-AB4D-87E4C2AAB1AA@microsoft.com...
>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(E3>0.5,E3,"")
>>
>> =ROUND(7/(C3-D3),5)
>>
>> Any suggestions please?
>>
>> Ted
>>
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > Try this:
>> >
>> > =IF(Z8>0.5,Z8,"")
>> >
>> > That will leave the cell "blank". Not to be confused with EMPTY.
>> >
>> > Biff
>> >
>> > "Ted" <Ted@discussions.microsoft.com> wrote in message
>> > news:CA960450-92C4-407D-9B24-FEA1ACE91F42@microsoft.com...
>> > >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
>> > >
>> >
>> >
>> >