formula not quite there

[dgavilanes]

Hi All,

my formula is almost there, it needs a litle tweaking.
If no data in a cell, I would like "-" if less than 10 "*"


=IF(D3<10,"*",IF(D3=" ","-",C3/D3))

Output should be

C D output
16 34 47.1
blank blank -
1 6 *


Thanks

Dennis

 

[oldchippy]

Hi Dennis,

Try this

=IF(AND(D3>0,D3<10),"*",IF(OR(C3="",D3=""),"-",C3/D3))

 

[NARAYANK991]

Hi Dennis ,

Just change your formula slightly :

=IF(D3="","-",IF(D3<10,"*",C3/D3))

The changes are as follows :

1. The space character in the check D3=" " is to be removed ; only then will the check return TRUE if D3 is blank.

2. The order of the checks is important ; if D3 is blank , it is also less than 10.

Narayan

 

[oldchippy]

@NARAYANK991

Perhaps I've read more into it?

 

[NARAYANK991]

Hi oldchippy ,

I am not sure whether I'm right ! Let us wait for Dennis to confirm.

Narayan

 

[dgavilanes]

T

Hi All,

my formula is almost there, it needs a litle tweaking.
If no data in a cell, I would like "-" if less than 10 "*"


=IF(D3<10,"*",IF(D3=" ","-",C3/D3))

Output should be

C D output
16 34 47.1
blank blank -
1 6 *


Thanks

Dennis

hanks

Hi Dennis ,

Just change your formula slightly :

=IF(D3="","-",IF(D3<10,"*",C3/D3))

The changes are as follows :

1. The space character in the check D3=" " is to be removed ; only then will the check return TRUE if D3 is blank.

2. The order of the checks is important ; if D3 is blank , it is also less than 10.

Narayan

I end it up using =IF(AND(D3>0,D3<10),"*",IF(OR(C3="",D3=""),"-",C3/D3))
it works just fine.

Thanks

Dennis

Hi oldchippy ,

I am not sure whether I'm right ! Let us wait for Dennis to confirm.

Narayan