Having problems with a circular reference and the #VALUE! error

I am trying to use excel for my P&L worksheet for work. There is one line
that i need to calculate the bonus that is going to be paid, and the way i do
that is by taking what is called the UCI for this year, subtract last year
UCI from it, then multiply the difference by 20%. Then I also have to add
100 to the line if two other requirements are met for a total of 200 added to
this line. so far this is what the cell contains.

=IF('Labor Calc'!C15>0,100*0.75,0)+IF('Weekly Call In
Sheet'!E6>0,100*0.75,0)+(H31*20%)

the problem that i am having is it's putting #VALUE! in the field. now,
because this cell is dependant on at least two other cells, it is throwing
everything off. now, if i edit one of the cells that is in this circular
reference to take this cell out, it corrects the #value! error, then i can
add this cell back into the formula, and everything works fine.

This is kinda hard to explain without actually showing someone the
worksheet, and i'll be glad to show you the work sheet if someone would be
willing to help me out and correct this problem. please respond... I need
to get this done as soon as possible to give to other managers in my company.

You might try breaking this down into several individual steps and making a
new formula for each step:

(from your instructions
1. UCI for this year, subtract last year UCI from it,
2. then multiply the difference by 20%.
3. Then I also have to add 100 to the line if two other requirements are
met for a total of 200 added to
this line.

( I'm not sure I understand the logic of step 3 in you instructions)



"celicagt1993" wrote:

> I am trying to use excel for my P&L worksheet for work. There is one line
> that i need to calculate the bonus that is going to be paid, and the way i do
> that is by taking what is called the UCI for this year, subtract last year
> UCI from it, then multiply the difference by 20%. Then I also have to add
> 100 to the line if two other requirements are met for a total of 200 added to
> this line. so far this is what the cell contains.
>
> =IF('Labor Calc'!C15>0,100*0.75,0)+IF('Weekly Call In
> Sheet'!E6>0,100*0.75,0)+(H31*20%)
>
> the problem that i am having is it's putting #VALUE! in the field. now,
> because this cell is dependant on at least two other cells, it is throwing
> everything off. now, if i edit one of the cells that is in this circular
> reference to take this cell out, it corrects the #value! error, then i can
> add this cell back into the formula, and everything works fine.
>
> This is kinda hard to explain without actually showing someone the
> worksheet, and i'll be glad to show you the work sheet if someone would be
> willing to help me out and correct this problem. please respond... I need
> to get this done as soon as possible to give to other managers in my company.
>