Macro for a comparison

[mike b]

Hello,

I would like the code for the following:

Compare the numbers in column I to column J (column I will always be a
bigger number). Display the result in column K in a percentage off high
(meaning column I) Its ok to display like: -7, no need to put the % sign.

example 49.35 is column I, 47 is column J column K should read -5, meaning
47 is 5% away from 49.35 and just display it as -5

Thanks in advance
--
Mike B

[K Dales]

You can do this easily just with standard calculations in column K; e.g. (for
K1):
= (I1-J1)/J1 (formatted properly, would give %)
= 100*(I1-J1)/J1 gives the number in the format you mention


"mike b" wrote:

> Hello,
>
> I would like the code for the following:
>
> Compare the numbers in column I to column J (column I will always be a
> bigger number). Display the result in column K in a percentage off high
> (meaning column I) Its ok to display like: -7, no need to put the % sign.
>
> example 49.35 is column I, 47 is column J column K should read -5, meaning
> 47 is 5% away from 49.35 and just display it as -5
>
> Thanks in advance
> --
> Mike B

[mike b]

How can I get this to go thru the 800+ lines of data? asutomatically?
I want it to do this with a macro and the amount of lines of data changes,
the data can be large so I dont want it limited until there is no more data
Thanks
Mike

"K Dales" wrote:

> You can do this easily just with standard calculations in column K; e.g. (for
> K1):
> = (I1-J1)/J1 (formatted properly, would give %)
> = 100*(I1-J1)/J1 gives the number in the format you mention
>
>
> "mike b" wrote:
>
> > Hello,
> >
> > I would like the code for the following:
> >
> > Compare the numbers in column I to column J (column I will always be a
> > bigger number). Display the result in column K in a percentage off high
> > (meaning column I) Its ok to display like: -7, no need to put the % sign.
> >
> > example 49.35 is column I, 47 is column J column K should read -5, meaning
> > 47 is 5% away from 49.35 and just display it as -5
> >
> > Thanks in advance
> > --
> > Mike B

[Rowan]

You could just enter the formula as given and drag it down to the botton of
your data but if you REALLY want a macro to do it then try:

Sub InsCalc()
Dim endRow As Long
endRow = Cells(Rows.Count, 9).End(xlUp).row
With Range(Cells(2, 11), Cells(endRow, 11))
.FormulaR1C1 = "=-100*(RC[-2]-RC[-1])/RC[-1]"
.NumberFormat = "0"
End With
End Sub

Regards
Rowan

"mike b" wrote:

> How can I get this to go thru the 800+ lines of data? asutomatically?
> I want it to do this with a macro and the amount of lines of data changes,
> the data can be large so I dont want it limited until there is no more data
> Thanks
> Mike
>
> "K Dales" wrote:
>
> > You can do this easily just with standard calculations in column K; e.g. (for
> > K1):
> > = (I1-J1)/J1 (formatted properly, would give %)
> > = 100*(I1-J1)/J1 gives the number in the format you mention
> >
> >
> > "mike b" wrote:
> >
> > > Hello,
> > >
> > > I would like the code for the following:
> > >
> > > Compare the numbers in column I to column J (column I will always be a
> > > bigger number). Display the result in column K in a percentage off high
> > > (meaning column I) Its ok to display like: -7, no need to put the % sign.
> > >
> > > example 49.35 is column I, 47 is column J column K should read -5, meaning
> > > 47 is 5% away from 49.35 and just display it as -5
> > >
> > > Thanks in advance
> > > --
> > > Mike B