Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with zero/# issue!!
I thought I was done but some other problem came up. What I'm doing is adding up cells to get a value x then adding another set of cells to get value y. I want to do this calculation x/y but getting the error because x is zero therefore won't compute. =(January!F47+February!F47+March!F47+April!F47+May !F82+June!F102+July!F67+August!F72+September!F62+O ctober!F47+November!F42+December!F42)/(January!G47+February!G47+March!G47+April!G47+May! G82+June!G102+July!G67+August!G72+September!G62+Oc tober!G47+November!G42+December!G42) This is what I have. I'm trying to figure out what I need to add to this equation to make that happen. I think I need do an IF statement stating if the value is 0 then calculate, if it doesn't then put a blank. This is important because lets say JanF47 is 1 but FebF47 is 0, the computation is not allowed. Also, on the bottom side of the division...if JanG47 is 1 but FebG47 is zero...doesn't like the calculation. I want the calculation to ignore the zeros. Hope you understand what I'm asking. I'm going to try several things to see if it will work. Thanks again for your help. Jason -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=491066 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with zero/# issue!!
One way:
=IF((January!G47+February!G47+March!G47+April!G47+ May!G82+June!G102+July! G67+August!G72+September!G62+October!G47+November! G42+December!G42)=0,"", (January!F47+February!F47+March!F47+April!F47+May! F82+June!F102+July!F67+ August!F72+September!F62+October!F47+November!F42+ December!F42)/(January! G47+February!G47+March!G47+April!G47+May!G82+June! G102+July!G67+August!G7 2+September!G62+October!G47+November!G42+December! G42)) In article , drvortex wrote: I thought I was done but some other problem came up. What I'm doing is adding up cells to get a value x then adding another set of cells to get value y. I want to do this calculation x/y but getting the error because x is zero therefore won't compute. =(January!F47+February!F47+March!F47+April!F47+May !F82+June!F102+July!F67+Augu st!F72+September!F62+October!F47+November!F42+Dece mber!F42)/(January!G47+Febru ary!G47+March!G47+April!G47+May!G82+June!G102+July !G67+August!G72+September!G6 2+October!G47+November!G42+December!G42) This is what I have. I'm trying to figure out what I need to add to this equation to make that happen. I think I need do an IF statement stating if the value is 0 then calculate, if it doesn't then put a blank. This is important because lets say JanF47 is 1 but FebF47 is 0, the computation is not allowed. Also, on the bottom side of the division...if JanG47 is 1 but FebG47 is zero...doesn't like the calculation. I want the calculation to ignore the zeros. Hope you understand what I'm asking. I'm going to try several things to see if it will work. Thanks again for your help. Jason |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with zero/# issue!!
didnt work. I believe the problem is within the equation, i have cells being empty or stating FALSE...therefore, when you add up all the cells and one (or a few) are empty or show FALSE...it will not compute properly. I'm trying to understand this but not working. I may have to send the spreadsheet to someone. Its hard to explain. -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=491066 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with zero/# issue!!
Using SUM() rather than the addition operator (+) will ignore text and
booleans. In article , drvortex wrote: didnt work. I believe the problem is within the equation, i have cells being empty or stating FALSE...therefore, when you add up all the cells and one (or a few) are empty or show FALSE...it will not compute properly. I'm trying to understand this but not working. I may have to send the spreadsheet to someone. Its hard to explain. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Issue with zero/# issue!!
I fixed it. I didn't change the above formula but went into the other cells and adjusted the formula. I had "" (double quotes) in the FALSE argument instead of just putting 0 (number zero). This then adds a zero instead of a BLANK which equals to a space but Excel doesn't represent that as a value but as a string. Anyway, all works now. -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=491066 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup issue ( not working and im tearing my hair out) | Excel Discussion (Misc queries) | |||
excel 2003 issue | Excel Discussion (Misc queries) | |||
Another rounding issue | Excel Worksheet Functions | |||
Sorting Issue (Mixde Fields) | Excel Discussion (Misc queries) | |||
Excel Viewer performance issue | Excel Discussion (Misc queries) |