| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How keep Excel from counting empty cells when calculating formula
I have plotted out a worksheet, but the problem is that it is trying to
calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating formula
Post your formula, then we can comment on it directly.
Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating formula
If your formula is =A1/B1, try =IF(B1="","",A1/B1) or
=IF(OR(A1="",B1=""),"",A1/B1) -- David Biddulph "Pete_UK" wrote in message oups.com... Post your formula, then we can comment on it directly. Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating form
=PRODUCT(J2,100/G2)
I'm doing a percentage formula. It works when the denominator is present, and I even tried it without the 100 factor but that didn't work either. I suppose another option would be if I would get Excel to tell me that J2 is x percent of G2. Don't know how to do that either. :-) "Pete_UK" wrote: Post your formula, then we can comment on it directly. Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating form
If you try to divide by G2 and G2=0 then you will get the #DIV/0
error, so this is the condition you need to trap - you can do it like this: =IF(OR(G2=0,G2=""),"",PRODUCT(J2,100/G2)) This will show a blank cell if G2 is zero, or blank. If you would prefer your cell to show zero, then change the second "" to 0. Hope this helps. Pete On Nov 8, 10:51 pm, Excel Noob wrote: =PRODUCT(J2,100/G2) I'm doing a percentage formula. It works when the denominator is present, and I even tried it without the 100 factor but that didn't work either. I suppose another option would be if I would get Excel to tell me that J2 is x percent of G2. Don't know how to do that either. :-) "Pete_UK" wrote: Post your formula, then we can comment on it directly. Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating form
That worked. Thanks to Pete UK and David Biddulph
"Pete_UK" wrote: If you try to divide by G2 and G2=0 then you will get the #DIV/0 error, so this is the condition you need to trap - you can do it like this: =IF(OR(G2=0,G2=""),"",PRODUCT(J2,100/G2)) This will show a blank cell if G2 is zero, or blank. If you would prefer your cell to show zero, then change the second "" to 0. Hope this helps. Pete On Nov 8, 10:51 pm, Excel Noob wrote: =PRODUCT(J2,100/G2) I'm doing a percentage formula. It works when the denominator is present, and I even tried it without the 100 factor but that didn't work either. I suppose another option would be if I would get Excel to tell me that J2 is x percent of G2. Don't know how to do that either. :-) "Pete_UK" wrote: Post your formula, then we can comment on it directly. Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help?- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How keep Excel from counting empty cells when calculating form
You're welcome.
Pete On Nov 8, 11:09 pm, Excel Noob wrote: That worked. Thanks to Pete UK and David Biddulph "Pete_UK" wrote: If you try to divide by G2 and G2=0 then you will get the #DIV/0 error, so this is the condition you need to trap - you can do it like this: =IF(OR(G2=0,G2=""),"",PRODUCT(J2,100/G2)) This will show a blank cell if G2 is zero, or blank. If you would prefer your cell to show zero, then change the second "" to 0. Hope this helps. Pete On Nov 8, 10:51 pm, Excel Noob wrote: =PRODUCT(J2,100/G2) I'm doing a percentage formula. It works when the denominator is present, and I even tried it without the 100 factor but that didn't work either. I suppose another option would be if I would get Excel to tell me that J2 is x percent of G2. Don't know how to do that either. :-) "Pete_UK" wrote: Post your formula, then we can comment on it directly. Pete On Nov 8, 10:30 pm, Excel Noob <Excel wrote: I have plotted out a worksheet, but the problem is that it is trying to calculate empty cells as having a value of zero. This means that whenever it is a divide function, it shows up as an error. I need it to count empty cells as having no value, or to just ignore them, so that it does not affect related formulas. Any help?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PRODUCT counting empty cells as 1 | Excel Worksheet Functions | |||
| the countA function is counting everything even empty cells | Excel Worksheet Functions | |||
| Calculating only non-empty cells... | Excel Worksheet Functions | |||
| Calculating slope, intercept, LOGEST with empty cells in data | Excel Worksheet Functions | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
Linear Mode
