Help calculating drawdown

[maani]

Hi,

I am trying to calculate the Drawdown of the cumulative Profit & Loss (P&L) and am having a difficulty in doing so. The issue is that the cumulative p&l has many cells in it that are '#na', thus I am getting an error when calculating the drawdown. I cannot change the cumulative p&l to empty or 0 for the cells in which it states '#na' because it is linked to a much larger spreadsheet. Thus, I want to know if anyone can help me to calculate the drawdown while keeping the cells in the cumulative p&l column (column a) as #na. Currently, cells a3:a137 are #na and the data begins in cell a138, but I would like the drawdown formula to incorporate cells a3 and beyond, as sometimes there will be data in these cells and sometimes it will be #na. My current formula for drawdown is:=IF(Isnumber(a3),a3-max($a$3:a3),a3). This simply returns #na in all of the cells. Could you please advise?

Thanks,

Maani

[windknife]

Try this,

B2

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter, then copied down

Hope this helps,
WindKnife

[maani]

Hi WindKnife,

Thanks for the reply. When I put the formula you suggested, it returned the result of '0' in all the cells until the #na result stops (b137), then from b138 onwards it just returned #na in the drawdown column. I'm looking to capture the drawdown in this case from b138 onwards in numerical form.

Maani

[maani]

My apologies. I went through and used the CTRL+SHIFT+ENTER function and it works! What does CTRL+SHIFT+Enter do? I have never used that combination within a formula. Thanks again!

Maani

[windknife]

Have you committed with Ctrl+Shift+Enter after inputing formula?

[windknife]

It's array formula. You can learn it from many articles in this site.

WindKnife

[maani]

Also, would there be a way to return the result #NA instead of 0 whenever the data hasn't started (in this case, cells a3:a137)? I tried to change this by changing the formula to #na at the end of it, but the result comes back as #name. The reason I am asking to change this is because for graphing purposes, #na doesn't take into account empty values, whereas 0 is graphed.

[windknife]

Try this,

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter

HTH,
WindKnife

[maani]

Thanks WindKnife