Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show zero if reference cell is zero? tricky!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show zero if reference cell is zero? tricky!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show zero if reference cell is zero? tricky!
Just use
=IF(S6=0,0,S5+S6) that will take care of empty cells as well -- Regards, Peo Sjoblom "Simon Lloyd" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to show zero if reference cell is zero? tricky!
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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
numbers being entered show in formula bar but not in cell? | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |