Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations plea
I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations plea
Ted,
The SUMIF function may do what you need. Here's an example. In cells A1 through A5 type the numbers 1, 2, 3, 4, and 5 respectively. In cell A6 try this formula to sum the numbers less than 3 (i.e., 1 and 2). =SUMIF(A1:A5,"<3",A1:A5) You can modify it for other results. "Ted" wrote: I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations plea
Hi!
Try this: =IF(Z80.5,Z8,"") That will leave the cell "blank". Not to be confused with EMPTY. Biff "Ted" wrote in message ... I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations plea
Ted, Sorry, I missed part of your question. Modify the formula in cell A6 like this to show blanks. =IF(SUMIF(A1:A5,"<3",A1:A5)=0,"",SUMIF(A1:A5,"<3", A1:A5)) Try it with less than 1, instead of less than 3, to see the blank result. =IF(SUMIF(A1:A5,"<1",A1:A5)=0,"",SUMIF(A1:A5,"<1", A1:A5)) "Ted" wrote: I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
Hi, thanks for the quick responses. Although, sorry but Im not sure that I
explained my situation very well: I am trying to subtract one set of figures from another (e.g. A1:A5-B1:B5). When the answer falls out side of a certain range (e.g. any answers is below, say, 4), I want to exclude them from all further calculations. The reason why I cant have zeros appearing in the cells with the answers is because I then run a correlation on the data meaning that it counts the zeros as data, altering the means an stuff. The only safe way I can get it to run the correlations and alike, is if the cells contain either the data/numbers I want to include in the correlation, or they are completely blank. I need cells to be completely blank after other calculations too! Any ideas please?? Thanks again, Ted. "Roland" wrote: Ted, Sorry, I missed part of your question. Modify the formula in cell A6 like this to show blanks. =IF(SUMIF(A1:A5,"<3",A1:A5)=0,"",SUMIF(A1:A5,"<3", A1:A5)) Try it with less than 1, instead of less than 3, to see the blank result. =IF(SUMIF(A1:A5,"<1",A1:A5)=0,"",SUMIF(A1:A5,"<1", A1:A5)) "Ted" wrote: I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
Hi, sorry - I think the suggestion from Biff may be working - I'm not sure if
this will work (using "" rather than 0) in all the ways I need it to though, so would still appriciate suggestions. thanks for all of your help, and thanks Biff for this quick fix $;-) Ted. "Biff" wrote: Hi! Try this: =IF(Z80.5,Z8,"") That will leave the cell "blank". Not to be confused with EMPTY. Biff "Ted" wrote in message ... I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
Ok, does anyone know how I can now add these two together, so I can run it
all in one cell please? I have added the to plug the zero problem, but are unsure how I would add these two: =IF(E30.5,E3,"") =ROUND(7/(C3-D3),5) Any suggestions please? Ted "Biff" wrote: Hi! Try this: =IF(Z80.5,Z8,"") That will leave the cell "blank". Not to be confused with EMPTY. Biff "Ted" wrote in message ... I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
Is cell E3 the result of C3 minus D3?
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
I AM TRYING TO KEEP A RUNNING TOTAL ON A FORM. tHE PROBLEM I AM HAVING IS
THAT WHEN I EXTEND THE FORMULA TO A ROW, IT CONTINUES THE TOTAL ALL THE WAY DOWN THE COLUM EVEN IF THERE IS NO VALUS IN THE SPOT BEFORE IT. I CAN SEND OVER THE FORM IF IT WILL HELP EXPLAIN. THE CURRNT FOMLA IS SUM(I4+H5) BUT IF H5 IS "0" THEN I DO NOT WANT IT TO TOTAL??? "Ted" wrote: Ok, does anyone know how I can now add these two together, so I can run it all in one cell please? I have added the to plug the zero problem, but are unsure how I would add these two: =IF(E30.5,E3,"") =ROUND(7/(C3-D3),5) Any suggestions please? Ted "Biff" wrote: Hi! Try this: =IF(Z80.5,Z8,"") That will leave the cell "blank". Not to be confused with EMPTY. Biff "Ted" wrote in message ... I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I replace zeros with blank spaces during calculations
Have you gone into Tools - Options and unenabled Zero Values yet?
Bob ps: Please don't post in caps as it's considered shouting. "j.r." wrote in message ... I AM TRYING TO KEEP A RUNNING TOTAL ON A FORM. tHE PROBLEM I AM HAVING IS THAT WHEN I EXTEND THE FORMULA TO A ROW, IT CONTINUES THE TOTAL ALL THE WAY DOWN THE COLUM EVEN IF THERE IS NO VALUS IN THE SPOT BEFORE IT. I CAN SEND OVER THE FORM IF IT WILL HELP EXPLAIN. THE CURRNT FOMLA IS SUM(I4+H5) BUT IF H5 IS "0" THEN I DO NOT WANT IT TO TOTAL??? "Ted" wrote: Ok, does anyone know how I can now add these two together, so I can run it all in one cell please? I have added the "" to plug the zero problem, but are unsure how I would add these two: =IF(E30.5,E3,"") =ROUND(7/(C3-D3),5) Any suggestions please? Ted "Biff" wrote: Hi! Try this: =IF(Z80.5,Z8,"") That will leave the cell "blank". Not to be confused with EMPTY. Biff "Ted" wrote in message ... I am trying to get excel to sum totals, but exclude answers that are outside of a certain range. I can get it to do this by using =IF(Z80.5,Z8,0). However, I need it to display absolutely nothing in the cells, rather than a zero because it alters future calculations. Has anyone any ideas please?? Ted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH | Excel Worksheet Functions | |||
How can I replace a range of blank cells with a 0 | Excel Discussion (Misc queries) | |||
Replace null string with blank cell | Excel Discussion (Misc queries) | |||
blank cells in calculations | Excel Worksheet Functions | |||
"False" filling in the blank spaces | Excel Discussion (Misc queries) |