Need to Understand what this formula is exactly doing
- Thread starter sanmaya
- Start date
vijay.vizzu
Member
If you can upload the file with the above formula. It can be easily explain the step by step for better understanding.
looking at the data, it looks like its simply extracting the digits to the left of the first period (.) and then the digits inside the () at the end..... there would be a much easier way of obtaining these results the the formula used.... but i could be missing something
=LEFT(A2,FIND(".",A2,1)-1)&" "&MID(A2,FIND("(",A2)+1,(FIND(")",A2)-(FIND("(",A2)+1)))
if this works, this would be much easier to breakdown... hahahhaha
i can attempt the original formula if you'd still like?
=LEFT(A2,FIND(".",A2,1)-1)&" "&MID(A2,FIND("(",A2)+1,(FIND(")",A2)-(FIND("(",A2)+1)))
if this works, this would be much easier to breakdown... hahahhaha
i can attempt the original formula if you'd still like?
Last edited:
NARAYANK991
Excel Ninja
Hi Sanmaya ,
There are simpler ways to achieve what this formula is doing , but understanding how it works will certainly help you learn new techniques.
First , let me correct the formula a bit ; since A1 in your file does not contain any data , the first formula should have A2 in place of A1 , as follows :
=-LOOKUP(0,-LEFT(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))&" "&-LOOKUP(1,-(" "&RIGHT(SUBSTITUTE(SUBSTITUTE(A2,")",""),"E","X"),{1,2,3,4,5,6,7,8,9,10,11,12})))
This formula should be entered in B2 , and copied down.
Let us start with the first building block.
1. Enter the following formula in C2 : =ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))
What this formula does is return an array of numbers starting from 1 , through till what ever is the length of text in cell A2. For your data , you will see the following array :
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44}
A simpler way of achieving the same result would be : =ROW(OFFSET($A$1,,,LEN(A2)))
This uses the volatile function OFFSET , but unless your data is in thousands of rows , it should not matter.
The next step is embedding this building block within the LEFT function , as follows :
=LEFT(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))
You can see that what will happen is that starting from the left-most character , the above formula will return an array of progressively longer strings ; thus , with the data in A2 , you should see the following :
{"1";"10";"100";"1000";"10003";"100033";"1000336";"10003367";"100033678";"100033678.";"100033678.U";"100033678.US";
I have only shown the first few elements of the resulting array.
Now , to a more complex building block , viz. the LOOKUP function.
This is a very strange function , and you will need to experiment with it to master it. With all its variants , it goes far beyond its cousins VLOOKUP / HLOOKUP.
In its most basic form , what it does is return the last element of an array. I suggest that you start by entering the following numbers in a range , say F1 through F9 :
1 , 10 , 100 , 1000 , 10003 , 100033 , 1000336 , 10003367 , 100033678
Now , enter the following formula in any unused cell , say E2 :
=LOOKUP(E1 , F1:F9)
a. With E1 blank , you should get the #N/A error value.
b. E1 = 0 returns the same error value
c. E1 = 1 returns 1
d. Experiment with different values in E1 , so that you can see that putting a very large value in E1 , which is bigger than the value in F9 ( 100033678 ) will return 100033678.
Thus , if the first parameter for the LOOKUP function is a very large number ( say 9^9 ) , then the LOOKUP function will return the last number in the array which has been passed to it as its second parameter. Note that the return value is the last value in the array , and not the biggest ; but for this , the first parameter has to be bigger than the biggest value in the array !
So , in your actual formula , why are we entering 0 as the first parameter ?
This is where the magic of the LOOKUP function lies !
As long as we can ensure that all the elements of the array are negative numbers , then 0 is a very big number !
To ensure that all the numbers are negative , we use the unary minus sign before the second parameter ; thus , if you change your formula : =LOOKUP(E1 , F1:F9) to :
=LOOKUP(E1 , -F1:F9)
with the unary minus before the second parameter , you can enter 0 in E1 , and see what the formula returns.
Of course , in your file formula , where the LEFT function returns strings , the unary minus does two things in one shot ; it converts the strings to numbers where ever possible , and also converts the numbers to negative values.
The problem now is that we have a negative result on our hands ; the simplest way to convert this back to its original positive form , is to use a second unary minus before the LOOKUP function , as in :
=-LOOKUP(E1 , -F1:F9)
Can you now break down the second LOOKUP function in your original formula ? All it does is :
1. Remove the last bracket ; why ?
2. Start from the right-most character instead of the left-most character.
Narayan
There are simpler ways to achieve what this formula is doing , but understanding how it works will certainly help you learn new techniques.
First , let me correct the formula a bit ; since A1 in your file does not contain any data , the first formula should have A2 in place of A1 , as follows :
=-LOOKUP(0,-LEFT(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))&" "&-LOOKUP(1,-(" "&RIGHT(SUBSTITUTE(SUBSTITUTE(A2,")",""),"E","X"),{1,2,3,4,5,6,7,8,9,10,11,12})))
This formula should be entered in B2 , and copied down.
Let us start with the first building block.
1. Enter the following formula in C2 : =ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))
What this formula does is return an array of numbers starting from 1 , through till what ever is the length of text in cell A2. For your data , you will see the following array :
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44}
A simpler way of achieving the same result would be : =ROW(OFFSET($A$1,,,LEN(A2)))
This uses the volatile function OFFSET , but unless your data is in thousands of rows , it should not matter.
The next step is embedding this building block within the LEFT function , as follows :
=LEFT(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))
You can see that what will happen is that starting from the left-most character , the above formula will return an array of progressively longer strings ; thus , with the data in A2 , you should see the following :
{"1";"10";"100";"1000";"10003";"100033";"1000336";"10003367";"100033678";"100033678.";"100033678.U";"100033678.US";
I have only shown the first few elements of the resulting array.
Now , to a more complex building block , viz. the LOOKUP function.
This is a very strange function , and you will need to experiment with it to master it. With all its variants , it goes far beyond its cousins VLOOKUP / HLOOKUP.
In its most basic form , what it does is return the last element of an array. I suggest that you start by entering the following numbers in a range , say F1 through F9 :
1 , 10 , 100 , 1000 , 10003 , 100033 , 1000336 , 10003367 , 100033678
Now , enter the following formula in any unused cell , say E2 :
=LOOKUP(E1 , F1:F9)
a. With E1 blank , you should get the #N/A error value.
b. E1 = 0 returns the same error value
c. E1 = 1 returns 1
d. Experiment with different values in E1 , so that you can see that putting a very large value in E1 , which is bigger than the value in F9 ( 100033678 ) will return 100033678.
Thus , if the first parameter for the LOOKUP function is a very large number ( say 9^9 ) , then the LOOKUP function will return the last number in the array which has been passed to it as its second parameter. Note that the return value is the last value in the array , and not the biggest ; but for this , the first parameter has to be bigger than the biggest value in the array !
So , in your actual formula , why are we entering 0 as the first parameter ?
This is where the magic of the LOOKUP function lies !
As long as we can ensure that all the elements of the array are negative numbers , then 0 is a very big number !
To ensure that all the numbers are negative , we use the unary minus sign before the second parameter ; thus , if you change your formula : =LOOKUP(E1 , F1:F9) to :
=LOOKUP(E1 , -F1:F9)
with the unary minus before the second parameter , you can enter 0 in E1 , and see what the formula returns.
Of course , in your file formula , where the LEFT function returns strings , the unary minus does two things in one shot ; it converts the strings to numbers where ever possible , and also converts the numbers to negative values.
The problem now is that we have a negative result on our hands ; the simplest way to convert this back to its original positive form , is to use a second unary minus before the LOOKUP function , as in :
=-LOOKUP(E1 , -F1:F9)
Can you now break down the second LOOKUP function in your original formula ? All it does is :
1. Remove the last bracket ; why ?
2. Start from the right-most character instead of the left-most character.
Narayan