Lotus 123 had a function that Excel doesn't

**Billybob K** · 08-01-2009, 12:33 PM

Lotus 123 had a function where you could graphically, in a cell, show plus symbols(+) or negative symbols (-) for the value of the data. For example if you had a column of numbers that you calculated from the previous year that indicate the change, Lotus, in the next cell would allow you to graphically display the symbols that indicate the strength of the change. So if your result was a positive ten then ten plus symbols would be shown. If it was -20 then 20 negative symbols would be shown.
Now the numbers I am looking at could be 600,00 and I believe there was a way to to proportion the symbols for example for every 100,000 put in one symbol so the result would be 6 (+) signs.

Does anyone remember this and can excel do it?

Thanks to all.

**DonkeyOte** · 08-01-2009, 12:39 PM

I'm not entirely sure I follow your example of 600,00 with one + per 100,000 being 6 "+" but... XL has the REPT function:

C1: =REPT(IF(B1>=A1,"+","-"),ABS(INT((B1-A1)/100)))

so the above would repeat either + or - depending on whether 2nd value >= 1st value... the character would be repeated x number of times where x was determined by 2nd value - 1st value divided by 100 ... so if A1 were 200 and B1 were 550 you would get 3 + in C1 (350/100 -> 3.5 rounded down to nearest whole number, ie 3)

Is that what you meant ?

**Billybob K** · 08-01-2009, 12:51 PM

Hang on- we are very close- thanks- will get back to you in a minute- still testing it.......

**Billybob K** · 08-01-2009, 01:19 PM

Don Qee-
I had to change your "-" and "+" around. And got the correct response. But when I went to a large number I got a #value# error on only one response- which really threw me (see attached).

I figured out that the value error was because there was not enough room on the worksheet- so I changed your divisor from 100 to 10000 and it worked.

Now- can this be turned into a macro or custom formula?

Thanks again

Bill K

**DonkeyOte** · 08-01-2009, 01:31 PM

I had to change your "-" and "+" around

Yes, the example was just that...

But when I went to a large number I got a #value# error on only one response- which really threw me (see attached).

The amount of characters that an XL cell can contain is limited, in XL2007 for ex. the limitation is 32767 characters, so to avoid errors you would put a MIN into the REPT function

D10: =REPT(IF(B10>=A10,"-","+"),MIN(32767,ABS(INT((B10-A10)/100))))

On earlier versions the limits will be different (lower) .. and/or the amount that can be viewed (outside of the formula bar) will vary... probably a good idea to avoid so many in the first instance...

Now- can this be turned into a macro or custom formula?

Confused, why do you wish to do that ?

General rule of thumb, Custom Functions (UDF) are slower than native functions...

**Billybob K** · 08-01-2009, 01:47 PM

Thank you for all your assistance. I will increase the divisor to avoid the max cell conflict.
I will also review the REPT function- that way I can stay native.

Bill K