trying to set formula to divide if criteria is met, keep getting a circular reference

[JORDANVC13]

Hello all-
I am a novice to excel and have stumbled my way through some reasonably advanced formulas, however, I can't figure this one out. I keep getting a circular reference. Here is the formula I have so far and then I'll explain what I am trying to do:
=IF(B8="","",SUM(R8,S8,VALUE(X8),IF(D8=0.5,O8/2)))

The last bold part is what I need to occur. I need the first part of the formula to calculate, then if D8=.5 to divide the result of the first part of the formula by 2. This formula is in cell O8 btw. The first half (non bold) works perfectly. Is what I want to accomplish possible? I've tried all I can to search for an answer, both here and the bigger webs and just can't seem to get close.

[Paul]

Is the reference to O8 near the end a typo? You said you want to divide the result of the SUM by 2, not cell O8 by 2...

There are issues with your formula, but this needs to be clarified first.

[JORDANVC13]

I apologize, I misspoke. The formula is in Cell O8 and that is also the cell I need to divide by two, once the first half of the formula is calculated. Is it better to create a reference cell for the first half and then divide/display that result in a separate cell?

[JORDANVC13]

The basic idea of what I am trying to accomplish is this:
The sheet is a payment calculator used to calculate my paycheck since my employer keeps screwing it up. there are a lot of different "IF" factors leading to the end result in cell O8, but at the end of all those calculations, I need that result to be divided by 2 if criteria is met in cell D8 (D8=.5). Sorry, Like I said, I am a novice and actually quite impressed I've made it this far. This is the last thing I can't figure out, was able to troubleshoot everything else. I really appreciate the help!

[FDibbins]

If your formula is in O8, you cannot then divide O8 by 2 and not get a circ ref. Perhaps try this...

=IF(B8="","",SUM(R8,S8,VALUE(X8)/IF(D8=0.5,2,1)))

What is in X8?

[JORDANVC13]

X8 is the result of another calculation. I have attached a sample of the file to the original post. Per forum rules, I hope.

[FDibbins]

I am at work atm, and cannot open files here

Is the answer in X8 already a value? If so, you dont need to use VALUE()

Did you try my revised logic for your calc?

[Paul]

I downloaded the file.

FDibbins' formula should work. As to why VALUE() is used for X8, it's because the IF function in that cell returns values like "$350", "$400", etc. instead of just 350, 400. Change those returns values to just numbers (no quotation marks, no dollars signs) and you won't need the VALUE(X8) function, just X8. You can then format X8 as currency with zero decimal places so you can see a $ sign if you want.

[FDibbins]

Paul, thanks for the confirmation

Jordan, I have opened your file and consolidated your formula in col X a little bit.
1. you dont need to keep testing for Q6<=300&, you could just use...
=IF(Q6>=300,Q6,IF(Q6<=300,IF(Y6="CVP","$350",IF(Y6="S","$400",IF(Y6="N","$300",IF(Y6="U","$300",IF(Y6="","$0")))))))

2. This could be further simplified a little further to...
=IF(Q6>=300,Q6,IF(Q6<=300,IF(Y6="CVP","$350",IF(Y6="S","$400",IF(OR(Y6="N",Y6="U"),"$300",IF(Y6="","$0"))))))

3. As Paul pointed out, by using "$350" etc in your formula, you are returning a text answer. If you just used plan ol 350 and formatted as Currency, you could save some extra time in your "O" formula. So X woukd then become...
=IF(Q6>=300,Q6,IF(Q6<=300,IF(Y6="CVP",350,IF(Y6="S",400,IF(OR(Y6="N",Y6="U"),300,IF(Y6="",0))))))

So your "O" formula evolves to...
=IF(B6="","",SUM(R6,S6,X6)/IF(D6=0.5,2,1))

[JORDANVC13]

Oh wow, you guys are awesome thank you. I’ll try those ASAP and hopefully be done with this project. Really appreciate the help and I’ll let ya know how it goes!

[FDibbins]

Happy to help, looking forward to see how you make out

[JORDANVC13]

FDibbins- Your formula works...almost. It is dividing the value in column X as needed, but I need it to divide the total in column O. Or alternatively, since that seems to create a circular reference, to divide columns R and S as well as X.
Since O is a total of the value in those 3 columns. Does that make sense?

[JORDANVC13]

For example- I've re-uploaded the file after applying your formula. However, if you look at line 12. "test" is the name. The formulas calculate correctly, until I enter .5 in column D. It divides it, but its off by $90. However, if I enter slightly different figures in line 13 "TEST 2", everything is calculated correctly....

[JORDANVC13]

hmmm nevermind! seems to be working fine now. that was weird. Much thanks to you both, I would not have been able to do this without you! YOU ROCK!

[FDibbins]

Great, thanks for the feedback