asked on manipulating a column in excel based on another column
i have two columns
a' column is a new monthly read value compared to the previous read which is in column b'
example
a b
100 (gallons feb Usage) 120 (gallons march usage)
unfortunately the two reading systems mutlipliers by gallons were not identical (one system read in feb, a different one ran in march)
so system a might read 100 gallons as '1' where as system b might read 100 gallons as '100'
I need excel to compare the value in column a to column b and create and manipulate column a's value.
example
a b c(newly created value by adding two zero's based on comparing a & b)
100 120000 100000 (it took the extra two zero's it identified by comparing a to b and added them
to column a's value and placed the result in c.
does this make any sense to anyone?
a' column is a new monthly read value compared to the previous read which is in column b'
example
a b
100 (gallons feb Usage) 120 (gallons march usage)
unfortunately the two reading systems mutlipliers by gallons were not identical (one system read in feb, a different one ran in march)
so system a might read 100 gallons as '1' where as system b might read 100 gallons as '100'
I need excel to compare the value in column a to column b and create and manipulate column a's value.
example
a b c(newly created value by adding two zero's based on comparing a & b)
100 120000 100000 (it took the extra two zero's it identified by comparing a to b and added them
to column a's value and placed the result in c.
does this make any sense to anyone?
Microsoft Excel
Last Comment
Jim Metcalf
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
in your example you seem to be looking for situation where 'b' is an order of magnitude greater than 'a' but its not clear if this is always consistent.
To follow the specific example you can use (in column c and assuming your data starts in row 2)
=A2*10^(LEN(A2)-LEN(B2))
which compares the length of a and b then multiples by 10 to the power of the difference. (so in this case it multiple by 10^2 or 100)
However whilst it works for the specific case it could fail (or worse provide a value that is wrong but looks ok at first glance), if you can provide more examples we can present a stronger solution
Crossed with Aikimark, his is a more robust solution
To follow the specific example you can use (in column c and assuming your data starts in row 2)
=A2*10^(LEN(A2)-LEN(B2))
which compares the length of a and b then multiples by 10 to the power of the difference. (so in this case it multiple by 10^2 or 100)
However whilst it works for the specific case it could fail (or worse provide a value that is wrong but looks ok at first glance), if you can provide more examples we can present a stronger solution
Crossed with Aikimark, his is a more robust solution
ASKER
worked like a charm!!!!!
that saved me an ton of work
that saved me an ton of work
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
In C1,C2...... just put the below formula and if you want to minus one from the other you can put a formula in D
A B C D
100 12000 = A1*100 =C1-B1
200 22000 = A2*100 =C2-B2
or you can do it in the below way too
A B C
100 12000 = B1-(A1*100 )
200 22000 = B2-(A2*100 )