Continue formula but without showing last total

=IF(SUM(C25:F25,G25)=0,"",SUM(H24,-C25:F25,G25))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"chieron" <chieron@discussions.microsoft.com> wrote in message
news:E62DF58C-C369-43C5-9215-781F0D254D25@microsoft.com...
> I have a formula that totals a balance of columns C to F and I wish to
copy
> this formula down to line 1000. I am currently upto line 25 and lines 26
to
> 1000 shows the last balance, ie 1200 all the way down to line 1000.
>
> The formula I am using is =H24-SUM(C25:F25)+G25
>
> What can I add to this formula to show lines 26 to 1000 as blank or 0, but
> will change when figures are entered in columns C-F.

When I put this formula in and put in the next entry at line 26 I get #VALUE!

My last total on line 25 is 1200 and when I spend 50 in column E, I should
get 1150 (also when I put in 50 I should get 1250). Instead I get a run of
#VALUE! 's


"Bob Phillips" wrote:

> =IF(SUM(C25:F25,G25)=0,"",SUM(H24,-C25:F25,G25))
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "chieron" <chieron@discussions.microsoft.com> wrote in message
> news:E62DF58C-C369-43C5-9215-781F0D254D25@microsoft.com...
> > I have a formula that totals a balance of columns C to F and I wish to
> copy
> > this formula down to line 1000. I am currently upto line 25 and lines 26
> to
> > 1000 shows the last balance, ie 1200 all the way down to line 1000.
> >
> > The formula I am using is =H24-SUM(C25:F25)+G25
> >
> > What can I add to this formula to show lines 26 to 1000 as blank or 0, but
> > will change when figures are entered in columns C-F.
>
>
>

That'll teach me. Try this instead

=IF(SUM(C26:F26,G26)=0,"",H25-SUM(C26:F26,G26))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chieron" <chieron@discussions.microsoft.com> wrote in message
news:205790A6-35FD-4B0A-9F13-D15285967383@microsoft.com...
> When I put this formula in and put in the next entry at line 26 I get
#VALUE!
>
> My last total on line 25 is 1200 and when I spend 50 in column E, I should
> get 1150 (also when I put in 50 I should get 1250). Instead I get a run of
> #VALUE! 's
>
>
> "Bob Phillips" wrote:
>
> > =IF(SUM(C25:F25,G25)=0,"",SUM(H24,-C25:F25,G25))
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "chieron" <chieron@discussions.microsoft.com> wrote in message
> > news:E62DF58C-C369-43C5-9215-781F0D254D25@microsoft.com...
> > > I have a formula that totals a balance of columns C to F and I wish to
> > copy
> > > this formula down to line 1000. I am currently upto line 25 and lines
26
> > to
> > > 1000 shows the last balance, ie 1200 all the way down to line 1000.
> > >
> > > The formula I am using is =H24-SUM(C25:F25)+G25
> > >
> > > What can I add to this formula to show lines 26 to 1000 as blank or 0,
but
> > > will change when figures are entered in columns C-F.
> >
> >
> >

That was a great help and it worked, thanks. Can you help me with something
else? It's similar to the one below and again I require blanks down to line
1000.

I have a formula to recognise if "yes" and "no" - when I copy this formula
down to line 1000 I get a run of FALSE

The formula I'm using is =IF(I13="yes",SUM(C13:F13)-G13,IF(I13="no","0"))
and this works fine when I put in yes or no but were it is still blank this
is where I get FALSE running down to line 1000.

Can you help??



"Bob Phillips" wrote:

> That'll teach me. Try this instead
>
> =IF(SUM(C26:F26,G26)=0,"",H25-SUM(C26:F26,G26))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "chieron" <chieron@discussions.microsoft.com> wrote in message
> news:205790A6-35FD-4B0A-9F13-D15285967383@microsoft.com...
> > When I put this formula in and put in the next entry at line 26 I get
> #VALUE!
> >
> > My last total on line 25 is 1200 and when I spend 50 in column E, I should
> > get 1150 (also when I put in 50 I should get 1250). Instead I get a run of
> > #VALUE! 's
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(SUM(C25:F25,G25)=0,"",SUM(H24,-C25:F25,G25))
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "chieron" <chieron@discussions.microsoft.com> wrote in message
> > > news:E62DF58C-C369-43C5-9215-781F0D254D25@microsoft.com...
> > > > I have a formula that totals a balance of columns C to F and I wish to
> > > copy
> > > > this formula down to line 1000. I am currently upto line 25 and lines
> 26
> > > to
> > > > 1000 shows the last balance, ie 1200 all the way down to line 1000.
> > > >
> > > > The formula I am using is =H24-SUM(C25:F25)+G25
> > > >
> > > > What can I add to this formula to show lines 26 to 1000 as blank or 0,
> but
> > > > will change when figures are entered in columns C-F.
> > >
> > >
> > >
>
>
>

You need an alternative to yes and no, in case it is neither. I will assume
"" here, so it would be

=IF(I13="yes",SUM(C13:F13)-G13,IF(I13="no","0",""))

If you just want 0 if not yes, regardless, then

=IF(I13="yes",SUM(C13:F13)-G13,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chieron" <chieron@discussions.microsoft.com> wrote in message
news:8182B4B2-E48D-4E83-B125-312B4EA96DE7@microsoft.com...
> That was a great help and it worked, thanks. Can you help me with
something
> else? It's similar to the one below and again I require blanks down to
line
> 1000.
>
> I have a formula to recognise if "yes" and "no" - when I copy this formula
> down to line 1000 I get a run of FALSE
>
> The formula I'm using is =IF(I13="yes",SUM(C13:F13)-G13,IF(I13="no","0"))
> and this works fine when I put in yes or no but were it is still blank
this
> is where I get FALSE running down to line 1000.
>
> Can you help??
>
>
>
> "Bob Phillips" wrote:
>
> > That'll teach me. Try this instead
> >
> > =IF(SUM(C26:F26,G26)=0,"",H25-SUM(C26:F26,G26))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "chieron" <chieron@discussions.microsoft.com> wrote in message
> > news:205790A6-35FD-4B0A-9F13-D15285967383@microsoft.com...
> > > When I put this formula in and put in the next entry at line 26 I get
> > #VALUE!
> > >
> > > My last total on line 25 is 1200 and when I spend 50 in column E, I
should
> > > get 1150 (also when I put in 50 I should get 1250). Instead I get a
run of
> > > #VALUE! 's
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =IF(SUM(C25:F25,G25)=0,"",SUM(H24,-C25:F25,G25))
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "chieron" <chieron@discussions.microsoft.com> wrote in message
> > > > news:E62DF58C-C369-43C5-9215-781F0D254D25@microsoft.com...
> > > > > I have a formula that totals a balance of columns C to F and I
wish to
> > > > copy
> > > > > this formula down to line 1000. I am currently upto line 25 and
lines
> > 26
> > > > to
> > > > > 1000 shows the last balance, ie 1200 all the way down to line
1000.
> > > > >
> > > > > The formula I am using is =H24-SUM(C25:F25)+G25
> > > > >
> > > > > What can I add to this formula to show lines 26 to 1000 as blank
or 0,
> > but
> > > > > will change when figures are entered in columns C-F.
> > > >
> > > >
> > > >
> >
> >
> >