| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#value!
Hi All, Search reveals nothing, Help is helpless (for me anyway). I know this needs an ISERROR or somethig like that but i can't get it to work. It works as long as there are values in B39 and C39. But sometimes B39 and/or C39 will not have a value, but there will always be a value in startingcash1 in which case i want whatever does have a value, to display. This is the problem formula: '=IF(startingcash1="","",(startingcash1+B39-C39)) This is in B39: '=IF(SUM(receipts1)=0,"",(SUM(receipts1))) This is in C39: '=IF(SUM(drops1)=0,"",(SUM(drops1))) Of course i could use B39 and C39 with a 0 instead of "" but then i have a 0 in those cells. Could use Conditional Formating to get rid of the 0 display. Must be an easier way. IE: '=IF(SUM(receipts1)=0,0,(SUM(receipts1))) '=IF(SUM(drops1)=0,0,(SUM(drops1))) Anyone give me a clue? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#value!
Not pretty but SUM ignores text (your "" is text)
=IF(startingcash1="","",SUM(startingcash1,B39)-SUM(C39)) -- Regards, Peo Sjoblom Portland, Oregon "Desert Piranha" <Desert.Piranha.21ytoa_1137814500.8068@excelforu m-nospam.com wrote in message news:Desert.Piranha.21ytoa_1137814500.8068@excelfo rum-nospam.com... Hi All, Search reveals nothing, Help is helpless (for me anyway). I know this needs an ISERROR or somethig like that but i can't get it to work. It works as long as there are values in B39 and C39. But sometimes B39 and/or C39 will not have a value, but there will always be a value in startingcash1 in which case i want whatever does have a value, to display. This is the problem formula: '=IF(startingcash1="","",(startingcash1+B39-C39)) This is in B39: '=IF(SUM(receipts1)=0,"",(SUM(receipts1))) This is in C39: '=IF(SUM(drops1)=0,"",(SUM(drops1))) Of course i could use B39 and C39 with a 0 instead of "" but then i have a 0 in those cells. Could use Conditional Formating to get rid of the 0 display. Must be an easier way. IE: '=IF(SUM(receipts1)=0,0,(SUM(receipts1))) '=IF(SUM(drops1)=0,0,(SUM(drops1))) Anyone give me a clue? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#value!
Hi Peo, Pretty cool arrangement of the formula, works fine. I learned something about formulas. Thanks very much Peo. Dave Peo Sjoblom Wrote: Not pretty but SUM ignores text (your "" is text) =IF(startingcash1="","",SUM(startingcash1,B39)-SUM(C39)) -- Regards, Peo Sjoblom Portland, Oregon "Desert Piranha" <Desert.Piranha.21ytoa_1137814500.8068@excelforu m-nospam.com wrote in message news:Desert.Piranha.21ytoa_1137814500.8068@excelfo rum-nospam.com... Hi All, Search reveals nothing, Help is helpless (for me anyway). I know this needs an ISERROR or somethig like that but i can't get it to work. It works as long as there are values in B39 and C39. But sometimes B39 and/or C39 will not have a value, but there will always be a value in startingcash1 in which case i want whatever does have a value, to display. This is the problem formula: '=IF(startingcash1="","",(startingcash1+B39-C39)) This is in B39: '=IF(SUM(receipts1)=0,"",(SUM(receipts1))) This is in C39: '=IF(SUM(drops1)=0,"",(SUM(drops1))) Of course i could use B39 and C39 with a 0 instead of "" but then i have a 0 in those cells. Could use Conditional Formating to get rid of the 0 display. Must be an easier way. IE: '=IF(SUM(receipts1)=0,0,(SUM(receipts1))) '=IF(SUM(drops1)=0,0,(SUM(drops1))) Anyone give me a clue? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
#value!
Thanks for the feedback
-- Regards, Peo Sjoblom Portland, Oregon "Desert Piranha" <Desert.Piranha.21ywom_1137818401.2319@excelforu m-nospam.com wrote in message news:Desert.Piranha.21ywom_1137818401.2319@excelfo rum-nospam.com... Hi Peo, Pretty cool arrangement of the formula, works fine. I learned something about formulas. Thanks very much Peo. Dave Peo Sjoblom Wrote: Not pretty but SUM ignores text (your "" is text) =IF(startingcash1="","",SUM(startingcash1,B39)-SUM(C39)) -- Regards, Peo Sjoblom Portland, Oregon "Desert Piranha" <Desert.Piranha.21ytoa_1137814500.8068@excelforu m-nospam.com wrote in message news:Desert.Piranha.21ytoa_1137814500.8068@excelfo rum-nospam.com... Hi All, Search reveals nothing, Help is helpless (for me anyway). I know this needs an ISERROR or somethig like that but i can't get it to work. It works as long as there are values in B39 and C39. But sometimes B39 and/or C39 will not have a value, but there will always be a value in startingcash1 in which case i want whatever does have a value, to display. This is the problem formula: '=IF(startingcash1="","",(startingcash1+B39-C39)) This is in B39: '=IF(SUM(receipts1)=0,"",(SUM(receipts1))) This is in C39: '=IF(SUM(drops1)=0,"",(SUM(drops1))) Of course i could use B39 and C39 with a 0 instead of "" but then i have a 0 in those cells. Could use Conditional Formating to get rid of the 0 display. Must be an easier way. IE: '=IF(SUM(receipts1)=0,0,(SUM(receipts1))) '=IF(SUM(drops1)=0,0,(SUM(drops1))) Anyone give me a clue? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=503572 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
