Adding asterisk to a value in column under certain criteria

[iotapsi321]

Hello. I am wondering how I would add an asterisk to numbers in column A only if the numbers in column B is in between two values? Thanks in advance for your time!

That’s the basic question. I’m creating a stats table and need to add one asterisk if a value is in between .05 and .011; I would add two asterisks if the value is in between .01 and .0011; and a third asterisk if the values are equal to or less than .001.
Here is what I am hoping to accomplish.
Column A Column B Column C
.56. .05 .56*
.73. .001. .73***
.02. .86. .02
.46. .06. .46
.68. .01. .68**

 

[Logit]

.
Assuming your numbers are in Cols A & B ...

Paste this formula in C1 and drage down :

=IF(B1<=0.001,CONCATENATE(B1,"***"),IF(B1<=0.01,CONCATENATE(B1,"**"),IF(B1<=0.05,CONCATENATE(B1,"*"),"")))

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

.56.​

[/td][td]

0.05​

[/td][td]

0.05*​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

.73.​

[/td][td]

0.001​

[/td][td]

0.001***​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

.02.​

[/td][td]

0.86​

[/td][td]

​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

.46.​

[/td][td]

0.06​

[/td][td]

​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

.68.​

[/td][td]

0.01​

[/td][td]

0.01**​

[/td][td][/td][/tr]
[/table]

 

[iotapsi321]

Thanks! By chance is there a way to make the cells that produce a false statement retain their values?

 

[Rick Rothstein]

Thanks! By chance is there a way to make the cells that produce a false statement retain their values?

Try replacing the "" at the end of Logit's formula with A1

 

[Logit]

Thanks! By chance is there a way to make the cells that produce a false statement retain their values?

Not certain I understand your question.

Do you mean not have a blank cell in Column C ? Rick's suggestion will fix that.

Or are you actually seeing the word FALSE in Column C ? The formula is written to avoid that.

 

[iotapsi321]

A few problems occurred. I got an error message saying the formula was too long. I also tried typing in A1 and no luck. I greatly appreciate your time.

 

[iotapsi321]

Oh and yes, I am actually seeing the word FALSE

 

[Rick Rothstein]

I got an error message saying the formula was too long.

You used the formula Logit posted in Message #2 and you got a message saying it was too long??? I do not see how that is possible.

 

[Logit]

.
One thing I see (which I corrected here but did not advise you - sorry) is your original numbers :

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

0.56​

[/td][td]

0.05​

[/td][td]0.05*[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

0.73​

[/td][td]

0.001​

[/td][td]0.001***[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

0.02​

[/td][td]

0.86​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

0.46​

[/td][td]

0.06​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

0.68​

[/td][td]

0.01​

[/td][td]0.01**[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Note, the numbers should NOT have a decimal at the end. I mistakenly left the decimal at the end in my original post. Not certain if that is
causing the error you see but it is a step in the right direction.

And, if you want to fill in the empty cells in Col C, follow previous advice of changing the very end of the formula from two quotes to A1.

 

[iotapsi321]

Figured it out. Thanks again!