Formula help please

  pontoon 22:42 14 May 2007
Locked

Trying to put a very simple formula into a sales log, but want the cells to remain blank untill figures have been entered, i'am trying to show gross profits as a %, the formula i've tried is b1(profit)/a1(sales),this returns correct answere, but if left blank returns #value, using open office calc. all advice welcome.

  VoG II 22:47 14 May 2007

=IF(AND(ISBLANK(A1),ISBLANK(B1)),"",B1/A1)

  pontoon 23:07 14 May 2007

Hi VoG thanks for the reply, tried the formula, but it comes up err508 ??

  pontoon 23:11 14 May 2007

Hi VoG thanks for the reply, tried the formula, but it comes up err508 ??

  VoG II 07:21 15 May 2007

Hmm. It works in Excel but I think I should have had an OR instead of an AND in any case. Try

=IF(OR(A1="",B1=""),"",B1/A1)

  silverous 09:24 15 May 2007

I think this is an Excel vs OpenOffice Calc difference in syntax.

I haven't used Calc but a look around suggests that the separator is ; rather than , in formalas.

On that basis you might find:

=IF(OR(A1="";B1="");"";B1/A1)

works. It should be OR because with AND it will only return blank if BOTH inputs are blank however you want it to return blank if either of them are.

  pontoon 14:45 15 May 2007

Thanks a lot Vog and silverous, that formula works great, and yes ; works instead of ,in calc.

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

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?