Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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'!C150,100*0.75,0)+IF('Weekly Call In Sheet'!E60,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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having problems with a circular reference and the #VALUE! error
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'!C150,100*0.75,0)+IF('Weekly Call In Sheet'!E60,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference Error | Excel Worksheet Functions | |||
Circular Reference Error Even Though There Is No C.R. | Excel Worksheet Functions | |||
Circular Reference error... | Excel Discussion (Misc queries) | |||
Getting rid of a circular reference error message | Excel Discussion (Misc queries) | |||
Circular reference error | Excel Discussion (Misc queries) |