Unable to fix a #VALUE! Error

[surfer1]

Hi guys, I'm experiencing a #VALUE! Error in my formula and I'm lost trying to fix it. I was hoping someone might be able to assist.

I have a portfolio manager with six columns, among others. They are -

Column A: L/S
Column B: Capital
Column C: Gross Sale
Column D: Buy fees
Column E: Sell fees
Column F: Profit/Loss

My formula (in column F) is designed to calculate the profit for either a short or long sale, designated by either an "L" or "S" in column A. Th error is appearing when there is no value inputted to the L/S column. Instead of the #VALUE! appearing in column F, I'd like it to be blank when no value is available for the Gross column.

My formula looks like this:

=IF(L/S="S", capital-gross-sell fees-buy fees, gross-capital-buy fees-sell fees)

The other formula I had was:
=IF(L/S="S", capital-gross-sell fees-buy fees, IF(L/S="L", gross-capital-buy fees-sell fees),"")

Thanks in advance. I'm also happy to attach the spreadsheet for further inspection. Cheers.

[southward]

Hi,

The "L/S" in your IF formula needs to be a cell reference. Such as A2, A3, A4...
The formula is looking for the presence of an "L" or an "S" in a cell in column A.
Then it will return the statement directed by the rest of the formula.

Cheers

[surfer1]

Hi Southward,

the formula is within a table. The actual cell reference is [@[L/S]]

L/S is simply the column heading. To clarify, the #VALUE! is appearing in Column F where there is no value in listed in the L/S column. Very sorry, I should have specified that at the beginning. The

Cheers.

[bebo021999]

Is it are you looking for?

A2="S" or "L"

B2:E2 is value:

=IFERROR(IF(A2="S",1,-1)*(B2-C2)-D2-E2,"NONE")

[southward]

Here you go:

Please Login or Register  to view this content.

[surfer1]

Hi Guys, here's a screenshot of the spreadsheet and formula.
Excel_Error.jpg

[bebo021999]

Is Gross, Sale and Buy fee a space returns from its formula, like =.....,"",... ?

Try to replace "" with value 0.

[surfer1]

I think the issue is stemming from the other column formulas whereby they contain "" instead on "0". My Profit/loss column could be reading the other columns that contain a formula and no numeric value and causing the result to display #VALUE! instead of displaying blank. Just a theory.