CODEWITHSUNDEEP
excelexcel-formula
Ariful Islam
Ariful Islam

Reputation: 674

Is it possible to declare a variable within formula and using it into same formula in excel?

I have a big excel formula like-

=CONCATENATE(  IF(  AND(LEN(A1)>3,LEN(A1)>=5),CONCATENATE( VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,5),2)),rng,2)," হাজার "),IF( AND(LEN(A1)>3,LEN(A1)=4),CONCATENATE( VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,4),1)),rng,2)," হাজার "),"")),IF(LEN(A1)>2,IF(NUMBERVALUE(LEFT(RIGHT(A1,3),1))>0,CONCATENATE(VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,3),1)),rng,2),"শত "),""),""),IF(NUMBERVALUE(IF(LEN(A1)>1, RIGHT(A1,2), RIGHT(A1,1)))>0,VLOOKUP( NUMBERVALUE(IF(LEN(A1)>1, RIGHT(A1,2), RIGHT(A1,1))),rng,2,FALSE),""))

In this formula I had to use 'A1' reference again and again. I need a function or anything like this-

=DEFVAR(A1,'somevar',CONCATENATE(  IF(  AND(LEN(somevar)>3,LEN(somevar)>=5),CONCATENATE(....)

So that I can use the formula on 'M9' cell like this-

=DEFVAR(M9,'somevar',CONCATENATE(  IF(  AND(LEN(somevar)>3,LEN(somevar)>=5),CONCATENATE(....)

Is there any way to achieve this?

Upvotes: 0

Views: 648

Answers (2)

Bathsheba
Bathsheba

Reputation: 234715

No, Excel formulas don't allow you to declare variables within them.

But what you could do in this case is use another cell that holds LEN(A1), and use that in your formula. Excel's very clever calculation cycle will guarantee that LEN(A1) is evaluated before any dependent formulas.

Essentially then you are using one or more other cells to hold "variables".

This also makes your spreadsheet easier to maintain. Avoid VBA if you can; (1) it's difficult to version-control, (2) not all companies permit use of .xlsm due to security issues, (3) VBA runs in a single thread.

Upvotes: 1

Rajesh Sinha
Rajesh Sinha

Reputation: 197

Your basic quetion is, "declare variable within formula and use it into the same formula".

Answer is NO.

Any Formula in Excel doesn't allows to declare variable and use it further. This is a common practice while programming and needless to say how, since somewhere you have written that you are using VBA!.

Now let me tell you how indirectly we use variables in Excel formula.

It's a Cell address which works as a variable. Other is Named Range which carries more than one value like an Array. Further more you can also use a formula as variable.

Let me say how, write a formula in cell and just Name it and use that Name in any formula like we call procedure while programming. A simple example I'm showing you here.

Write formula is A2, = if(A5>=1000,Max(B2:B100),0) and just Name it like MYMAX. Then in other formula you can call it like, =if(B2 = "A01",MYMAX,"Nothing").

Hope this help you to realize that, why we need Programming.

Upvotes: 1

Related Questions