Excel Formula

  Abel 14:45 21 Apr 2008
Locked

May I please have your help with the following Excel formula, which is in cell DY11. The intention, is to achieve either a blank cell, one with zero (0.00) value or an actual two decimal number value.
IF(ISBLANK(DX11),"",IF(DX11=0,0,DX11)) Cell DX11, apart from containing a formula, is blank, but the formula returns a zero (0.00) value in cell DY11.

Abel

  VoG II 15:01 21 Apr 2008

In DY11

=IF(DX11="","",DX11)

and format DY11 as Number with 2 decimal places.

  Abel 15:23 21 Apr 2008

Thanks VoG™, I guessed you'd be the one to reply. However, I'm still not achieving my aim. The formula you suggest, produces either a blank or an actual value cell, but not a cell with a zero (0.00) value. Cell DX11, should either have a blank, zero or actual value.

Abel

  Abel 15:26 21 Apr 2008

Sorry, for Cell DX11, should either have a blank, zero or actual value read Cell DY11

Abel

  VoG II 15:33 21 Apr 2008

What determines whether DY11 should display 0.00?

With my formula above, if DX11 = 0 then DY11 will display 0.00.

  Abel 16:38 21 Apr 2008

Cell DX11 contains the following formula: IF(DW11="","",IF(DW11>0,ROUNDDOWN(DW11,0)*20%,0)). Cell DX11 will remain blank, unless data is generated. Conversely, if a negative or positive value is generated in DW11, then in cell DX11 either a zero value will ensue if negative, or an actual value if positive. A bit verbose, but I hope the explanation is understandable?
I've tried the formula: IF(DX11="","",IF(DX11=0,0,DX11)) in cell DY11, but it remains blank if DX11 has a zero value.

Abel

  VoG II 16:49 21 Apr 2008

So a negative number in DX11 should generate 0.00 in DY11? Try

=IF(DX11="","",MAX(DX11,0))

  Abel 17:05 21 Apr 2008

Something rather weird is happening! With the intention of seeking your permission to e-mail you an extract of the offending spreadsheet, I copied part of it to fresh one. However, while your suggested formula didn't work on the original sheet, it did on the fresh one. I can't understand it, but I'll do my utmost to find out and come back to you.

Abel

  VoG II 17:18 21 Apr 2008

Perhaps in Tools > Options > Display you set the option to not show zero values?

  Abel 17:43 21 Apr 2008

In my version of Excel 2003, Tools> Options> View, under Window options, 'Zero Values' is checked. Thanks for the suggestion though.

Abel

  Abel 14:42 22 Apr 2008

Thank you for your help yesterday VoG™. However, I never did get to the bottom of the anomaly between the two spreadsheets. In the end, I solved my problem by rethinking my requirements, and tweaking the formula 'IF(DX11="","",IF(DX11=0,0,DX11))' you suggested, but instead of working off cell DX11, used DT11 instead. Without seeing the spreadsheet this will probably sound strange to you but, let me say, it works!
It's great being able share and solve problems on this forum.

Abel

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone