# if whet randbetween

why dose

=IF(AND(B4="",B8="")=1,"",
IF((B4/2)*$N$52>=RANDBETWEEN(1,100),"!!! JA !!!",
IF((B8)*$N$51>=RANDBETWEEN(1,100),"!!! JA !!!","NEJ")))


not work it returns #VALUE!

edit retag close merge delete

Dear sad_dog, are you sure that to say JA (rarely) and NEJ (frequently) when filling two cells you need so complicated formula?

( 2017-02-23 18:48:06 +0100 )edit

it's part of a much bigger spreadsheet whet conditions when to even display the certain values

( 2017-02-24 16:03:06 +0100 )edit

N51 and N52 can only be 1 or 0, so thy act as a switch not a value

( 2017-02-24 16:59:44 +0100 )edit

Sort by » oldest newest most voted

Ok, so I played around with the formula. The reason why the formula might return a #VALUE error is if one of your cells (B4, B8, N52 or N51) contains a non-number value. For instance - if one of those four cells contain letter (A-Z) or if one of the cells contains exclamation marks, quotation marks etc - then the formula will return an error. Make sure that B4, B8, N52 or N51 only contain numbers.

more

ok found it. Seem's like it can't handle "" as in empty. google spreadsheet just ser "" as no value rater then a text

( 2017-02-24 16:04:24 +0100 )edit

this fixed it =IF(AND(B4="",B8="")=1,"",IF((IF(ISTEXT(B4),0,B4)/2)$N$52>=RANDBETWEEN(1,100),"!!! YES !!!",IF(IF(ISTEXT(B8),0,B8)$N$51>=RANDBETWEEN(1,100),"!!! YES !!!","No")))

( 2017-02-24 16:41:00 +0100 )edit