Complicated ISBLANK Cell Formatting Question

[JohnPL]

Hello,

Typically I would grind away for hours trying to solve the problem myself, that way I will never forget how to do it again, but this time, I don't even know where to start!

Little confusing to explain, but here is my best shot:

In order to keep my table neat, I have the following equation designed to hide a value if the feeding cell is blank (redacted company info!):

=IF(ISBLANK([@[Retail]]),"",([@[Retail]]-[@[Unit Cost]])*(([@[TTL PCS]]))) - "Profit Column"

In the next column I would like to calculate the profit margin (cell formatted for percentage), but I am having trouble keeping the cell blank if there is no value entered in the "Retail" cell:

=IF(ISBLANK([@[Profit]]),"",([@[Profit]]/[@[COGS]])) - "Profit Margin Column

Rather than hiding the value when "Retail" is blank, it simply shows the error #VALUE! for the "Profit Margin" cell. When a value IS entered in the "Retail" cell the problem goes away and the profit margin is calculated just fine.

The crux of the issue is that I would like to hide values from showing in the Profit Margin column if the Profit cell is empty. My equation worked the first time, for calculating Profit, but it won't seem to work for the next column, profit margin. I simply get the "#VALUE!" error for the cells being fed from an empty profit cell.

I think the problem might stem from the different cell formatting, profit being formatted for currency, while the profit margin is formatted for percentage. Maybe I need to have a custom cell format?

I recognize that is a little confusing, so any help is much appreciated.

Thanks,
John

 

[Aladin Akyurek]

You seem to say that this pair does not succeed as expected when Retail is blank/emty:

=IF([@[Retail]]="","",([@[Retail]]-[@[Unit Cost]])*(([@[TTL PCS]])))

=IF([@[Profit]]="","",([@[Profit]]/[@[COGS]]))

Right?

How about?

=IF([@[Retail]]="","",([@[Retail]]-[@[Unit Cost]])*(([@[TTL PCS]])))

=IF([@[Retail]="","",([@[Profit]]/[@[COGS]]))

 

[JohnPL]

Wow, easy fix. That should have been the very first thing I tried! I was clearly over-thinking this just a bit.

Thanks for your help, I really appreciate it.

John

 

[Aladin Akyurek]

Wow, easy fix. That should have been the very first thing I tried! I was clearly over-thinking this just a bit.

Thanks for your help, I really appreciate it.

John

You are welcome.