if cell < than 0.05 then *

[cmendes]

Greetings,

If there a formula in which I can ask if the value of a particular cell is smaller then 0.05, 0.01 and 0.001 to generate "*", "**" or "***" respectively?
I have a excel table with all the p values and i would like to have the * for each one.
Thanks in advance,
C

 

[Aladin Akyurek]

Greetings,

If there a formula in which I can ask if the value of a particular cell is smaller then 0.05, 0.01 and 0.001 to generate "*", "**" or "***" respectively?
I have a excel table with all the p values and i would like to have the * for each one.
Thanks in advance,
C

One of:

=VLOOKUP(A2,Table,2,0)

=LOOKUP(A2,{0,0.001,0.01,0.05},{"","*","**","***"})

Does any of these meet your intent?

 

[Scott Huish]

=rept("*",match(a1,{0.05,0.01,0.001},-1))

 

[Greg Truby]

I actually interpreted this backward from the way Aladin did. I'm probably wrong - but on the off chance I ain't, using Aladin's I'd get:

• =IF(ISNUMBER(A2),LOOKUP(A2,{0,0.001,0.01,0.05},{"***","**","*",""}),"")

 

[cmendes]

Actually none of the suggestions worked... Maybe i did not explain myself.
if a value is smaller than 0.05 (not equal!) then it gives *
if it is smaller then 0.01 (not equal!) then gives **
and if it's smaller then 0.001 (not equal!) it gives ***

the closest I got was the fomula
=REPT("*",MATCH(A1,{0.05,0.01,0.001},-1))
but it considers "smaller or equal" not "smaler"

Thanks,
C

 

[Aladin Akyurek]

Actually none of the suggestions worked... Maybe i did not explain myself.
if a value is smaller than 0.05 (not equal!) then it gives *
if it is smaller then 0.01 (not equal!) then gives **
and if it's smaller then 0.001 (not equal!) it gives ***

the closest I got was the fomula
=REPT("*",MATCH(A1,{0.05,0.01,0.001},-1))
but it considers "smaller or equal" not "smaler"

Thanks,
C

A2: 0.05
A3: 0.003
A4: 1
A5: 0
A6: 0.01
A7: 0.001
A8: -0.02

Care to state the expected outcomes for the above values?

 

[cmendes]

I would expect:
N/A
**
N/A
***
*
**
***

instead, I am getting
<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">*</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">**</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="center" height="20">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">***</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">**</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">***</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">***</td> </tr> </tbody></table>
the problem, as I said, was that it has to be smaller, not smaller or equal...

 

[Scott Huish]

=rept("*",match(a1+0.000000000000001,{0.05,0.01,0.001},-1))

 

[cmendes]

yes! I see the trick! ; )
Thank you all for the prompt responses!
Best,
C

 

[Aladin Akyurek]

I would expect:
N/A
**
N/A
***
*
**
***

instead, I am getting
<TABLE border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" height=20 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>**</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20 align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>***</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>**</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>***</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>***</TD></TR></TBODY></TABLE>
the problem, as I said, was that it has to be smaller, not smaller or equal...

Looks something like:

=IF(ISNUMBER(A2),IF(A2<0.001,"***",IF(A2<0.01,"**",IF(A2<0.05,"*","N/A"))),"")