Formula to pick a value and add stars(*) in a different cell

[jskasango]

I need a formula to place the value from C3 to E3 and add stars(*) if D3 meets 3 conditions. 1) If D3 multiply by 2 is less than 4, one star. 2) If D3 is greater than 4 but less than 6, two stars. 3) If D3 is greater than 6, three stars.

 

[jtakw]

The boss has not yet replied but just a quick one, how do I include (NumberFormat = "0.00") in the formula so as to suppress excess decimals?

You can ROUND C3 or use TEXT like Fluff suggested:

Book3.xlsx
	C	D	E
3	-5.19	2.57	-5.19 *
4	10.89	2.57	10.89 *
5	12.19	5	12.19 **
6	-19.69	12.59	-19.69 ***
7	37.15678	4	37.16 *
Sheet948

Cell Formulas
Range	Formula
E3:E7	E3	=ROUND(C3,2)&IF(D3>=6," ***",IF(D3>4," **"," *"))

 

[jskasango]

You can ROUND C3 or use TEXT like Fluff suggested:

Book3.xlsx
	C	D	E
3	-5.19	2.57	-5.19 *
4	10.89	2.57	10.89 *
5	12.19	5	12.19 **
6	-19.69	12.59	-19.69 ***
7	37.15678	4	37.16 *
Sheet948

Cell Formulas
Range	Formula
E3:E7	E3	=ROUND(C3,2)&IF(D3>=6," ***",IF(D3>4," **"," *"))

Thanks a lot! The boss has now made it even more complicated for me, he wants the formula to treat negative values as if they are positive without removing the negative (-) sign. Is this really possible? -0.01 to -4.00 "*", -4.01 to -5.99 "**", -6.00 onwards "***". Please help

You can ROUND C3 or use TEXT like Fluff suggested:

Book3.xlsx
	C	D	E
3	-5.19	2.57	-5.19 *
4	10.89	2.57	10.89 *
5	12.19	5	12.19 **
6	-19.69	12.59	-19.69 ***
7	37.15678	4	37.16 *
Sheet948

Cell Formulas
Range	Formula
E3:E7	E3	=ROUND(C3,2)&IF(D3>=6," ***",IF(D3>4," **"," *"))

 

[Fluff]

How about

=TEXT(C3,"0.00 ")&REPT("*",IF(ABS(D3)<=4,1,IF(ABS(D3)<=6,2,3)))

 

[jtakw]

Thanks a lot! The boss has now made it even more complicated for me, he wants the formula to treat negative values as if they are positive without removing the negative (-) sign. Is this really possible? -0.01 to -4.00 "*", -4.01 to -5.99 "**", -6.00 onwards "***". Please help

This will do it:

Book3.xlsx
	C	D	E
3	-5.19	2.57	-5.19 *
4	10.89	2.57	10.89 *
5	12.19	5	12.19 **
6	-19.69	12.59	-19.69 ***
7	37.15678	4	37.16 *
8	10.89	-2.57	10.89 *
9	12.19	-6	12.19 ***
10	-19.69	-12.59	-19.69 ***
11	37.15678	-0.02	37.16 *
12	12	6	12 ***
Sheet948

Cell Formulas
Range	Formula
E3:E12	E3	=ROUND(C3,2)&IF(ABS(D3)>=6," ***",IF(ABS(D3)>4," **"," *"))

 

[jskasango]

This will do it:

Book3.xlsx
	C	D	E
3	-5.19	2.57	-5.19 *
4	10.89	2.57	10.89 *
5	12.19	5	12.19 **
6	-19.69	12.59	-19.69 ***
7	37.15678	4	37.16 *
8	10.89	-2.57	10.89 *
9	12.19	-6	12.19 ***
10	-19.69	-12.59	-19.69 ***
11	37.15678	-0.02	37.16 *
12	12	6	12 ***
Sheet948

Cell Formulas
Range	Formula
E3:E12	E3	=ROUND(C3,2)&IF(ABS(D3)>=6," ***",IF(ABS(D3)>4," **"," *"))

Wow! Now I believe excel can do anything!!!!! Thanks a lot!!!!

 

[jtakw]

You're welcome, thanks for the feedback.

 

[jskasango]

Another option

=TEXT(C3,"0.00 ")&REPT("*",IF(D3<=4,1,IF(D3<=6,2,3)))

The boss has changed the lock just when I MrExcel has given me the key! The new instruction is treat the values as absolute, thus from -2 to 2 "ns", from >2 to 4 "*", >4 to 6, "**", anything >6, "***". This means all negatives should be treated as positives. I hope this is possible.

 

[Fluff]

How about

=TEXT(C3,"0.00 ")&IF(ABS(D3)<=2,"ns",IF(ABS(D3)<=4,"*",IF(ABS(D3)<=6,"**","***")))

 

[jskasango]

How about

=TEXT(C3,"0.00 ")&IF(ABS(D3)<=2,"ns",IF(ABS(D3)<=4,"*",IF(ABS(D3)<=6,"**","***")))

Thanks a lot @Fluff , I hope the boss does not change the lock again! I really appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.