Continue formula but without showing last total

[chieron]

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.

 

[Bob Phillips]

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


--

HTH

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

 

[chieron]

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]

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]

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]

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)