| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a
percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
If you don't want to show 0 or #Div/0 results.. then =IF(OR(A2="",B2="",B2=0),"",A2/B2) or =IF(SUM(A2,B2)=0,"",A2/B2) -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111335 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
Try this
=IF(COUNT(A2:B2)=2,A2/B2,"") Mike "Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
Try
=IF(ISERROR(A2/B2),"",A2/B2) If this post helps click Yes --------------- Jacob Skaria "Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
=IF(N(B2),A2/B2,"")
"Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage formul
Hi,
Try =IF(B1=0,"",A1/B1) or the shorter =IF(B1,A1/B1,"") And regarding the second question: You need to protect the worksheet - Select all the cells you want to be unlocked and choose Format, Cells, Protection, and uncheck Locked. Next choose Tools, Protection, Protect Sheet... -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage fo
Thanks Jacob.
I have two questions: 1. I am also trying to add contents of different cells like A1, B1, C1, D1....and my target cell is G1. If all the cells from A1 to F1 are blank then the result I get in G1 is '0'. How can I get a blank instead of a '0'. 2. Also, if one of the cells (say B1) has a special character like "*" (denoting no data available) then the result I get in the target cell G1 is "#VALUE!". How can I make the target cell (G1) blank while still having the formula. Please help. Rum "Jacob Skaria" wrote: Try =IF(ISERROR(A2/B2),"",A2/B2) If this post helps click Yes --------------- Jacob Skaria "Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to not get '0' or '#DIV/0!' in a cell with a percentage fo
The formula using COUNT() given for the other post should handle * as
well..if you are using SUM() If this post helps click Yes --------------- Jacob Skaria "Rum" wrote: Thanks Jacob. I have two questions: 1. I am also trying to add contents of different cells like A1, B1, C1, D1....and my target cell is G1. If all the cells from A1 to F1 are blank then the result I get in G1 is '0'. How can I get a blank instead of a '0'. 2. Also, if one of the cells (say B1) has a special character like "*" (denoting no data available) then the result I get in the target cell G1 is "#VALUE!". How can I make the target cell (G1) blank while still having the formula. Please help. Rum "Jacob Skaria" wrote: Try =IF(ISERROR(A2/B2),"",A2/B2) If this post helps click Yes --------------- Jacob Skaria "Rum" wrote: I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a percentage formula. Example: Column A2 = Numerator Column B2 = Denominator Then for % formula I do the following in column A1: =A2/B2 And this gives me a the cell A1 with percent formula but if the cells A2 and B2 are blank then it gives "#DIV/0!". Can we design this formula in such a way that the target cell (A1) remains empty but still has the formula? Also, how can I lock the formula cells so that no one can change it? Please help. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can you modify the background color of a cell by using formul. | Excel Worksheet Functions | |||
| adding a formul to the next cell only with the next cell number | Excel Discussion (Misc queries) | |||
| Can you change the relative reference of a cell in an Excel formul | Excel Discussion (Misc queries) | |||
| Is there a function to determine whether a cell contains a formul. | Excel Worksheet Functions | |||
| can you use a variable or cell reference in a getpivotdata formul. | Excel Worksheet Functions | |||
Linear Mode
