#NAME? error on if statement

[Blake 7]

Hi Guys, i am using the following formula in cell F5:F7

Please Login or Register  to view this content.

It works in F7 but not F5 or F6 and is resulting in #name? error... and im not sure why this could be! any suggestions would be appreciated!

I have attached the workbook if that helps.

Many thanks

[Ron Coderre]

You have odd quotes in the first REPT function.
These: “”
Should be: ""

Actually, check all of your quotes. I found more of those odd ones.

Also, I'm curious why you'd want to repeat an empty text string more than once.
This: REPT("",10-LEN(FIXED(E7*100,2,TRUE))
will always simply return: ""

[Blake 7]

Hi Ron and thanks for taking time to help. I'll amend the formula to get rid of the wonky speech marks and hopefully that will cure the issue!

As for your question...ha! I have a dashboard which I want to $exy up a bit and came across this link http://chandoo.org/wp/2006/09/05/3-s...ards-in-excel/ and simply thought I'd be adventurous and try it out! I actually have no idea how the formula works, I just like the result! Would you recomened a more efficient method to get the same result?

All the best

[Blake 7]

Hello Ron, Success! I made the amendment you sugessted et voila.... perfect. Thanks again

[Ron Coderre]

I think I'd consider using something like this in your workbook

Please Login or Register  to view this content.

Does that help?

[Blake 7]

oh my goodness! thats a lot shorter!

I just popped it into the workbook and it did the trick!

Thanks, that kind of you. I'm going home now to celebrate with a beer!

All the best

[Ron Coderre]

Actually, you can take advantage of Excel's custom number format capability's recognition of positive, negative and zero values and use something like this:

Please Login or Register  to view this content.

[Blake 7]

ooh, i'll give this a whirl now!! beer in hand! cheers!!