Unable to Solve a #VALUE! Error

Hi, I have created a calculation for a share portfolio that says,
"If ('closing price' is BLANK, then BLANK, if ('Side' says L, then calculate A, If ("Side' says B, then calculate))) - 100%

I've formatted the cell to percentage which displays corrected when Closing Price is populated, but if Blank, it produce the #VALUE! error. How do I get this cell stay blank if the closing price is blank. I've attached a screenshot for a reference.
Thanks.

Excel Error_2018.07.31_19h09m00s_012_.jpg

An image isn't much use to us - better to attach the Excel workbook.

The problem is that you are subtracting 100% (or 1) from the result of the nested-IF term, and if that results in "" then you will get the #VALUE error. You can subtract the 1 from the two individual terms which produce number values within the IF function.

Hope this helps.

Pete

Hi Pete, cheers. Subtracting 1 from each IF statement has fixed the #VALUE! error. I did get an 'Inconsistent formula' dialogue appear but has now disappeared. Thanks for that.

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete