Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
I need to have a worksheet that other people can use with out changing and my
problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
Donna,
You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
Thank you so much, that worked great! One more thing. Is there a way to do
like a "conditional format" that turns the error #DIV/0! the same color as the background so that you can't see it? I thought I had done this before but now I can't get it to work. "Mike H" wrote: Donna, You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
In the conditional format box
The formula is =ISERROR(A1) Jan Donna wrote: Thank you so much, that worked great! One more thing. Is there a way to do like a "conditional format" that turns the error #DIV/0! the same color as the background so that you can't see it? I thought I had done this before but now I can't get it to work. "Mike H" wrote: Donna, You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
Hi,
This depends on the version of Excel you are using, lets suppose the first cell of the selected range is A1: In 2003: 1. Select the cell you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula only cells that contain 4. From the Format only cells with pick Errors =ISERR(A1) 5. Click the Format button 6. Choose a White (or whatever color the cell background is) on the Font tab 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: 5. Click the Format button and choose the Number tab. 6. Pick the Custom category and 7. On the Type line enter ;;; 8. Click OK as many times as necessary If this helps, please click the Yes button. Cheers, Shane Devenshire "Donna" wrote: Thank you so much, that worked great! One more thing. Is there a way to do like a "conditional format" that turns the error #DIV/0! the same color as the background so that you can't see it? I thought I had done this before but now I can't get it to work. "Mike H" wrote: Donna, You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
This doesn't work. I highlighted the area went to format/conditional
formatting and put formula and then =ISERROR(A1) and I formatted it to be the same color as the background to hide the error but it just doesn't seem to work. I have tried =ISERROR before, could it be the type of error (#DIV/0!)? "Jan Kronsell" wrote: In the conditional format box The formula is =ISERROR(A1) Jan Donna wrote: Thank you so much, that worked great! One more thing. Is there a way to do like a "conditional format" that turns the error #DIV/0! the same color as the background so that you can't see it? I thought I had done this before but now I can't get it to work. "Mike H" wrote: Donna, You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dividing by 0 error
Why would you use A1? You should obviously use the same cell where the error
is so if the error range is M1:M100 you select that range with M1 as the active cell and then use =ISERROR($M1) -- Regards, Peo Sjoblom "Donna" wrote in message ... This doesn't work. I highlighted the area went to format/conditional formatting and put formula and then =ISERROR(A1) and I formatted it to be the same color as the background to hide the error but it just doesn't seem to work. I have tried =ISERROR before, could it be the type of error (#DIV/0!)? "Jan Kronsell" wrote: In the conditional format box The formula is =ISERROR(A1) Jan Donna wrote: Thank you so much, that worked great! One more thing. Is there a way to do like a "conditional format" that turns the error #DIV/0! the same color as the background so that you can't see it? I thought I had done this before but now I can't get it to work. "Mike H" wrote: Donna, You could test for Div/0 and return "" if it is or use this to sum with those errors in the range = SUMIF(F1:F6,""&0) Mike "Donna" wrote: I need to have a worksheet that other people can use with out changing and my problem is that I need to have a certain amount of blank cells that when a number is entered will fill in cells that contain formulas. Some of the formulas contain division and so I'm getting the #DIV/0! error which prevents the columns from summing. How can I say to only sum if there is a number in the cell so that it won't include the error. Or how can I keep the error from showing but also have the blank cells for people to use? In other words only "formulate" when there are numbers. I hope this makes sense.... # of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts across 10 500 20 100 1290 65.00 5 600 30 90 43.00 20 200 80 320 17.00 - #DIV/0! - #DIV/0! - #DIV/0! Total #DIV/0! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dividing zero by zero | Excel Worksheet Functions | |||
Dividing by Zero | Excel Discussion (Misc queries) | |||
hiding error message when dividing by 0 | Excel Worksheet Functions | |||
Dividing name | Excel Worksheet Functions | |||
Dividing by zero | Excel Discussion (Misc queries) |