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?
jamesmetcalf74Asked:
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.

Kanti PrasadCommented:
Hi

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  )
0
aikimarkCommented:
Place this formula in C1 and fill down as needed.
=A1*POWER(10,INT(LOG10(B1)-INT(LOG10(A1))))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
regmigrantCommented:
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
0
jamesmetcalf74Author Commented:
worked like a charm!!!!!
that saved me an ton of work
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.