# My PowerPivot formula works on individual components but combination gives error

I try to create a formula in Power Pivot, where I try to generate a new column (or even better a measure if anybody can help on that

=mid([Placement],11,1) returns correctly the 11th character, if the [Placement] column has characters
=if(len([Placement])>0,1,2) returns correctly either 1 or 2 dependent on the length of [Placement]

But combining the two into one new Column like below generates an error

=if(len([Placement])>0,mid([Placement],11,1),2)

Urgent help is needed.

best regards

Jørgen
LVL 4
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
=if(len([Placement])>10,mid([Placement],11,1),2)

I don't see how that could be a measure - it doesn't really make sense to me.
ConsultantAuthor Commented:
Hi Rory,

Most of the Placement records are actually blank, which is the reason for selecting 0. and I tried the measure part as well, and it did not make sense for me either. But I thought it was my lack of knowledge on the measures.

I will check if inserting 10 makes a difference, but I imagine it does not. Do you find any errors in the formula apart from the 10 figure?

best regards

Jørgen
ConsultantAuthor Commented:
#ERR
Commented:
If you have any records that are less than 11 characters, you'd get an error, which is why I suggested changing to > 10.
Commented:
Oh wait - will the 11th character always be a number? If so, use:
=if(len([Placement])>10,mid([Placement],11,1)+0,2)
If not, use:
=if(len([Placement])>10,mid([Placement],11,1),"2")

You can't have different data types returned by the two result arguments.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

ConsultantAuthor Commented:
But I did not get the error on testing
=if(len([Placement])>0,1,2)
and I do also get an error using 10 - no difference at all :-(
Commented:
See my last post. :)
ConsultantAuthor Commented:
Hi Rory
Worked like a charm, when I inserted the +0.

Do you by any chance have any experience on the average ytd functionality in Power Pivot (in that case I will post another question)?

best regards

Jørgen
ConsultantAuthor Commented:
Great solution - and worth to remember in the future
Commented:
I can't say I've ever used it but feel free to post it and I'm sure we can figure it out. :)
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.