Formula to show zero if reference cell is zero? tricky!

[Simon Lloyd]

Hi all, I have a formula that looks at the cell value on one sheet and adds it to the previous value on the original sheet, my problem is this....where this formula appears it shows the last summed value so in this case from S6 down to Sxxx it will show the value £813 because this is the last value it added, in the formula below i would like it to state that if 'Account Entry'!S6 is zero then dont calculate S6+S5 just show zero! is this possible in a formula? =IF('Account Entry'!S6="","0",'Account Entry'!S6+S5)

Regards,
Simon

[Tim M]

In your sample formula it looks like you are checking to see if S6 is a blank
cell, if so enter a 0. you could try =IF('Account Entry'!S6=0,"0",'Account
Entry'!S6+S5)


"Simon Lloyd" wrote:

>
> Hi all, I have a formula that looks at the cell value on one sheet and
> adds it to the previous value on the original sheet, my problem is
> this....where this formula appears it shows the last summed value so in
> this case from S6 down to Sxxx it will show the value £813 because this
> is the last value it added, in the formula below i would like it to
> state that if 'Account Entry'!S6 is zero then dont calculate S6+S5 just
> show zero! is this possible in a formula? =IF('Account
> Entry'!S6="","0",'Account Entry'!S6+S5)
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=551646
>
>

[Peo Sjoblom]

Just use

=IF(S6=0,0,S5+S6)

that will take care of empty cells as well

--


Regards,

Peo Sjoblom


"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all, I have a formula that looks at the cell value on one sheet and
> adds it to the previous value on the original sheet, my problem is
> this....where this formula appears it shows the last summed value so in
> this case from S6 down to Sxxx it will show the value £813 because this
> is the last value it added, in the formula below i would like it to
> state that if 'Account Entry'!S6 is zero then dont calculate S6+S5 just
> show zero! is this possible in a formula? =IF('Account
> Entry'!S6="","0",'Account Entry'!S6+S5)
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=551646
>

[Simon Lloyd]

Thanks for the reply guys!, the cells that the formula looks at on another sheet do not always have a zero in them, my problem is that if i copy my formula down in a column and say the first 3 figures are 1,2,3 then S4 will look at S4 on the Account Entry sheet and then add S3 off the sheet that is doing the calculation, if i have copied the formula down then assuming S4 on the Account Entry sheet is 0 every cell i copied down to will display 6 i would like them to display 0 until there is a value in S4 on the Account Entry sheet!

Hope its possible!

Simon