Question about "IF" formula

[choth21]

I have some questions about this formula:

=++IF(Circ,$M$60,0) * L88

1) Does the "++" do anything?

2) Do the extra spaces around the "*" operator do anything?

3) The named cell "Circ" has something to do with allowing the circular reference in the workbook and leads to a cell with a hard coded value of "1". I've vaguely seen this technique before, but am not sure what it does in this formula because the "IF" condition" is just a named cell and not an operation or direction to go to another cell. I've created models before that allowed circular references and it worked out fine. What does the 1 do and how does this formula work?

Any help is appreciated.

Thanks.

[Sam Capricci]

to your first and second question I would say no to each. Don't know anything about your third question, never seen that before.

[Richard Buttrey]

It's usually beneficial to upload the workbook so that we can see the question in context. There might be other stuff that impinges on an explanation and it's also useful if you exxplain what your system is doing and what you expect this particular formula to achieve.


However given the description and that the named cell 'circ' contains a formula that links to a cell containing the number 1 then:

1. No the ++ makes no difference, apart from asking Excel to make two unnecessary calculations.
2. No, the spaces make no difference to the calculation.
3. The one does nlothing at all.

In the IF formula there is no test associated with the 'circ' (i.e. the value 1) and hence Excel treats this as a True condition and immediately evaluates M60 * L88

[choth21]

Thanks! It looks like I haven't posted enough on this forum to post links or images. #1 & #2 make sense. I think from the last reply, I understand what the model's author is doing.

[Sam Capricci]

if those answers solve your issue then you can mark the post as solved using the thread tools dropdown at the top of the post.
You can upload a workbook if needed by clicking on "go advanced" below the quick reply window, then midway down click on manage attachments, then at the top use browse to locate the file, upload, close window and submit reply to upload a workbook.

[choth21]

Got it. Thanks! Uploaded a sheet from the model. The "Circ" cell is D7. Hopefully, this one sheet is enough. Had to delete some things due to confidentiality.

Thanks.

[Sam Capricci]

it is difficult to debug it because every time I follow a formula to what is in it all of them are making some connection to a #REF! error.
to me, the question is, what should the formula be doing?
Could you have uploaded a version with some hard coded non confidential data so the reference errors weren't showing as much?

[choth21]

Understood. Uploaded the full model now. Thanks much.

[Sam Capricci]

I can't find what role Circ actually plays, still a lot of REF errors in the sheet where the formula points.

So I just put in numbers to see what happened, I changed Circ from 1 to 2 then to 1000 and put in numbers into the cells where the sums in M60 and E60 come from and I found no impact by changing the 1 to 2 or to 1000 with the output in M88 where that formula is.

I also did some finds >> ++ and replaces >> left blank and didn't see any impacts to the formula outputs that were there.

Beyond that I don't know how much more help I can offer without knowing what the formulas are supposed to do - at least by getting rid of all the ref errors where they appear with real numbers.

I'd recommend you make a copy workbook and make some changes to the formulas then check it against the original workbook with the same referenced values to see what if any impact they have.

[choth21]

Thanks for taking a look.