|
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. |
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. |
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. |
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. |
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. |
Formula not consistent
|
Formula not consistent
sounds as though the best bet would be to create a macro to go through the
cells and enter a 0 for those that are blank "SLP" wrote: Hi, The file is too large to be sent as an attachment. What the formula does is: Rate Per Month * Hours Per Day - Vacation Hours Scheduled * Percent of Time for the Cost Center (as folks can be in multiple Cost Centers) So I should see a value if rate, hours per day, percent is filled in whether or not they take vacation. "Richard" wrote: if you can send me an example of the workbook I can sort this, it doesn't sound difficult only I need to see it. you'll also have to tell me what you'd like to see instead of #value. If you are happy to do this send it to "SLP" wrote: 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. |
Formula not consistent
That is an idea. Thanks.
"Richard" wrote: sounds as though the best bet would be to create a macro to go through the cells and enter a 0 for those that are blank "SLP" wrote: Hi, The file is too large to be sent as an attachment. What the formula does is: Rate Per Month * Hours Per Day - Vacation Hours Scheduled * Percent of Time for the Cost Center (as folks can be in multiple Cost Centers) So I should see a value if rate, hours per day, percent is filled in whether or not they take vacation. "Richard" wrote: if you can send me an example of the workbook I can sort this, it doesn't sound difficult only I need to see it. you'll also have to tell me what you'd like to see instead of #value. If you are happy to do this send it to "SLP" wrote: 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. |
| All times are GMT +1. The time now is 11:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com