Formulas referencing a spaces instead of number ?

[Ainsley]

Ive imported my Trial Balance into one tab. I want to analyse this information on other tabs in various ways and apply some simple sums to the values. But when I link these cells to my Trial Balance and there is no actual number I get an error if I try to apply a formula to it. It sees a blank as a space, not the absence of an actual value.

How do I sum a range of these cells when some are spaces. Is there a formula that I can use to copy the entire TB and only copy the values, ignoring the spaces ? Then this can be the TB I analyse.

[Dave Peterson]

You can use a formula like:

=sum(a1:b9)

This will ignore cells that are empty and cells that have text in them.

If you want to retrieve a value from a cell, you can use a formula like:
=sheet2!a1

But this will show 0 if that cell is empty.

=if(sheet2!a1="","",sheet2!a1)

will make it look like the cell is blank if that other cell is empty.

Ainsley wrote:
>
> Ive imported my Trial Balance into one tab. I want to analyse this
> information on other tabs in various ways and apply some simple sums to
> the values. But when I link these cells to my Trial Balance and there
> is no actual number I get an error if I try to apply a formula to it.
> It sees a blank as a space, not the absence of an actual value.
>
> How do I sum a range of these cells when some are spaces. Is there a
> formula that I can use to copy the entire TB and only copy the values,
> ignoring the spaces ? Then this can be the TB I analyse.
>
> --
> Ainsley
> ------------------------------------------------------------------------
> Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
> View this thread: http://www.excelforum.com/showthread...hreadid=538042

--

Dave Peterson

[Ainsley]

Ive tried those formulas which unfortunatly dont work.

My Trial Balance has some numbers and some spaces in it. The spaces are causing problems as whatever formulas I use I get errors as I cant add up numbers and spaces. The spaces are caused by my accounting package displaying an abscence of a value as a space, as if you used your space bar to clear out a cell.

How do I remove the "spaces" so there is nothing in the cell ?

Regards, Ainsley

[Dave Peterson]

=sum() will ignore the text values in that range.

But it sounds like your "numbers" aren't numbers. They are text masquerading as
numbers.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ainsley wrote:
>
> Ive tried those formulas which unfortunatly dont work.
>
> My Trial Balance has some numbers and some spaces in it. The spaces
> are causing problems as whatever formulas I use I get errors as I cant
> add up numbers and spaces. The spaces are caused by my accounting
> package displaying an abscence of a value as a space, as if you used
> your space bar to clear out a cell.
>
> How do I remove the "spaces" so there is nothing in the cell ?
>
> Regards, Ainsley
>
> --
> Ainsley
> ------------------------------------------------------------------------
> Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
> View this thread: http://www.excelforum.com/showthread...hreadid=538042

--

Dave Peterson

[Ainsley]

Ah, thats great. Its solved my problem by just using =sum().

Made my day, cheers.