Formula not consistent

[SLP]

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.

 

[Richard]

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,"",ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation

Schedule'!I6)*ProgramOverview!$AR6,2))

Richard

 

[SLP]

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]

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*'Emp
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]

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]

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
(e-mail address removed)

 

[SLP]

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]

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]

That is an idea. Thanks.

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