Formula not giving expected results

[billfinnjr]

Good morning!
I ran into an interesting issue yesterday. A formula that has worked correctly since it was written by a predecessor 2 years ago gave a very unexpected result. Formula is is follows;
=(IF(A256="Andersen 200",Windows!C9,IF(A256="Andersen 400",Windows!C15,IF(A256="JW Builders Vinyl w/J Chnl",Windows!C23,IF(A256="JW Brickmould Vinyl w/J Chnl",Windows!C28,IF(A256="JW Flat Casing w/J Chnl",Windows!C33,IF(A256="Paradigm",Windows!C38,IF(A256="Paradigm Triple",Windows!C43,IF(A256="Paradigm Flat Casing",Windows!C48, 0))))))))&IF(A256="MasterView Vinyl", Windows!C53, IF(A256="MasterView Vinyl Flat Casing",Windows!C60,IF(A256="Mathew Bros Vinyl",Windows!C67,IF(A256="Mathew Bros Vinyl Flat Casing",Windows!C73, 0)))))*1

Apparently the fellow who wrote the formula ran into the nested IF limit and had to add to the formula using the &. Yesterday I had to select the Anderson 400 and instead of seeing a cost of $183 as expected I saw $1830. I checked a couple of things, evaluated the formula and it was $183 all the way until the very last step. Then it changed to $1830. Hmmm... I changed the "&" to "+" and now all is well. Thought it might save someone else some time in the future.
Thanks,
Bill

[jason.b75]

For your information, that nested IF limit is much higher now, unless you're still using a really old version of excel (2003 or prior).

Also there are many alternative methods that are much neater.

=CHOOSE(MATCH(A256,List,0),Windows!C9,Windows!C15,etc...) would be my method of choice. Where List refers to a list of all the possible names that could be entered into A256.

[billfinnjr]

Jasonb75,
Thanks very much for your response. I am aware of the increased limits but am restricted to using Excel 2000 for the foreseeable future. There are 160 or so variant of this formula that I have on the list to rewrite when I get a chance, but I'm under the gun on this one so it it functions and gives expected results every time it stays for now. Maybe in phase 3 I can finish the cleanup.
Thanks much!
Bill

[jason.b75]

From what I can see of your formula, it may have been giving incorrect results for sometime, but has only just been noticed
The way the formula is written, any result from the nested IF before the & would have incorrectly had a trailing zero joined to it.

For decimal values, this would not be a problem 1.2 and 1.20 are both the same, but if you have integers (in your case, amounts exactly to the dollar) then the result would be wrong.

[billfinnjr]

Jason.b75,
Agreed. This was the part of the project that was supposed to be "ALL SET" when they handed it over to me. I have now removed the "&" from all formulas in this project. It just so happened that I used options that are rarely used and that is when the issue showed up.
Thanks
Bill