| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula not consistent
Hi. I'm stumped. I have a formula:
=ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2) Sometimes I works fine and other times I have to go to the VacationSchedule sheet and enter a zero when there isn't a number in the cell in order to not have the #Value error show. Any ideas what it could be? Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula not consistent
It looks as though some of the cells that are being used for calculation are
not empty, but contain a space " ", which cannot be rounded. A simple solution to this would be to use the following formula which checks for the error and saves you having to put 0 in the blank cells =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"",ROU ND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) Richard "SLP" wrote: Hi. I'm stumped. I have a formula: =ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2) Sometimes I works fine and other times I have to go to the VacationSchedule sheet and enter a zero when there isn't a number in the cell in order to not have the #Value error show. Any ideas what it could be? Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula not consistent
Thanks but that formula results in no value. I did an =isblank and you are
right, there is something in the cells that look blank which is causing the error. Any other ideas? "Richard" wrote: It looks as though some of the cells that are being used for calculation are not empty, but contain a space " ", which cannot be rounded. A simple solution to this would be to use the following formula which checks for the error and saves you having to put 0 in the blank cells =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"",ROU ND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) Richard "SLP" wrote: Hi. I'm stumped. I have a formula: =ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2) Sometimes I works fine and other times I have to go to the VacationSchedule sheet and enter a zero when there isn't a number in the cell in order to not have the #Value error show. Any ideas what it could be? Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula not consistent
If you want something to appear even for the "blank" cells then you have to
enter something in the quotes after true, in the suggested formula. for example =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"This Cell IS Blank",ROUND(EmployeeData!AM6*(EmployeeData!$P6*'E mp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) will return the comment This cell is blank. if you don't want a text comment enter a different formula and remove the "" "SLP" wrote: Thanks but that formula results in no value. I did an =isblank and you are right, there is something in the cells that look blank which is causing the error. Any other ideas? "Richard" wrote: It looks as though some of the cells that are being used for calculation are not empty, but contain a space " ", which cannot be rounded. A simple solution to this would be to use the following formula which checks for the error and saves you having to put 0 in the blank cells =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"",ROU ND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) Richard "SLP" wrote: Hi. I'm stumped. I have a formula: =ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2) Sometimes I works fine and other times I have to go to the VacationSchedule sheet and enter a zero when there isn't a number in the cell in order to not have the #Value error show. Any ideas what it could be? Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula not consistent
If I go to the vacation schedule sheet and enter a zero in I6 it works fine.
If I then go back to the vacation schedule sheet and delete the zero it works fine. (It being my formula). I see what the person's true salary is for the month I am on. Any other thoughts? I guess I can just have my end users always enter a zero if someone is not scheduled to take vacation in a given month but they will moan about having to enter zeros. Funny thing it is not happening on every row. "Richard" wrote: If you want something to appear even for the "blank" cells then you have to enter something in the quotes after true, in the suggested formula. for example =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"This Cell IS Blank",ROUND(EmployeeData!AM6*(EmployeeData!$P6*'E mp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) will return the comment This cell is blank. if you don't want a text comment enter a different formula and remove the "" "SLP" wrote: Thanks but that formula results in no value. I did an =isblank and you are right, there is something in the cells that look blank which is causing the error. Any other ideas? "Richard" wrote: It looks as though some of the cells that are being used for calculation are not empty, but contain a space " ", which cannot be rounded. A simple solution to this would be to use the following formula which checks for the error and saves you having to put 0 in the blank cells =if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"",ROU ND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2)) Richard "SLP" wrote: Hi. I'm stumped. I have a formula: =ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2) Sometimes I works fine and other times I have to go to the VacationSchedule sheet and enter a zero when there isn't a number in the cell in order to not have the #Value error show. Any ideas what it could be? Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula not consistent
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates not consistent | Excel Discussion (Misc queries) | |||
| Sub-Totals not consistent | Excel Discussion (Misc queries) | |||
| Dir command not consistent | Excel Programming | |||
| Keep consistent between two worksheets | Excel Discussion (Misc queries) | |||
| Excel formula to make AAA-BBB equal to/consistent with BBB-AAA | Excel Worksheet Functions | |||
Hybrid Mode
