adding label with result

**johnmk** · 01-28-2010, 12:53 PM

I am new at excel and what I want to do is to add a "*","**","***" to a result in a cell depending on a choice from another cell. This a frame sheet for a car that I have broken the car into 3 zones and depending on the zone picked I just want to add the star to that result so it represents the area damaged. I hope that makes sense.

**Paul** · 01-28-2010, 01:06 PM

Hi Johnmk, welcome to the forum.

Could you perhaps provide a sample workbook showing your layout and the results you're expecting. While we can make guesses as to what you need, it will probably be beneficial to see it in action to get it right more quickly.

Thanks!

**johnmk** · 01-28-2010, 01:29 PM

Ok I will try to do my best, =)

cell A1 is a drop down box of three choices "zone 1", "zone 2", "zone 3". then in the box in question here is the formula

=IF(C20>=16,2.5,IF(C20>=11,1.5,IF(C20>=8,1,IF(C20>=4,0.5,IF(C20>0,0.3,0))))*M25)

what I want to do is for what ever the value is say 1.0 that is in the cell I want depend on what was chosen in the dropdown box of either "zone 1" which would yeild 1.0* or "zone 2" which would show 1.0** or say "zone 3" which would show 1.0*** Does that make sense?

**johnmk** · 01-28-2010, 01:30 PM

I am just wanting to add the stars at the end of the result depending on the zone picked

**Paul** · 01-28-2010, 01:39 PM

Using LOOKUP formulas rather than multiple IF statements, you could try:

=IF(C20>=16,2.5,LOOKUP(C20,{0,0.001,4,8,11},{0,0.3,0.5,1,1.5})*M25)&LOOKUP(A1,{"Zone 1","Zone 2","Zone 3"},{"*","**","***"})

Using your original IF(IF(IF structure, it would be:

=IF(C20>=16,2.5,IF(C20>=11,1.5,IF(C20>=8,1,IF(C20>=4,0.5,IF(C20>0,0.3,0))))*M25)&LOOKUP(A1,{"Zone 1","Zone 2","Zone 3"},{"*","**","***"})

Note, though, that by concatenating the asterisks to the end, the cell is no longer a numeric value. It becomes text.

**johnmk** · 01-28-2010, 01:48 PM

Sweet! Thanks for you help it worked prefect.

**Paul** · 01-28-2010, 01:53 PM

Glad I could help!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save